Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA question #2

0

Now that my original question was answered, where I wanted to skip to the next row after a certain condition was met in row 2. Now, I would like the cursor to go back to the colmn 1 after I've entered data in column 7. (See updated VBA program).

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    If Not Intersect(Target, Range("A2:A1000")) Is Nothing Then

        With Target(1, 2)

        .Value = Now

        .EntireColumn.AutoFit

        Cells(Target.Row, 3).Select

         End With

    End If

End Sub

Answer
Discuss

Discussion

Please update the title of your question to reflect its content so future readers can more easily find what they are looking for:)
don (rep: 1989) Dec 20, '21 at 8:22 am
Add to Discussion

Answers

0

Zac

Assuming you want to move to the next empty cell in column A (after you've chnaged or entered text in column 7 = G), then you can add a second Intersect test to the same event macro (see section in bold below) to check if column G was changed. If so, I've used a new variable (undeclared), LastRwA, to see which last row in A contains something then used a Range statement to select the cell below.

Note that I changed my previous Cells () approach to move to column C (so that the macro uses a consistent method of addressing cells)- see the second bold line below.

The first bold line below sets Application.EnableEvents to  False (so that this event macro isn't triggered again when it changes column C or anything else you get it to do). That gets turned back on at the end (otherwise such macros won't work).

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    Application.EnableEvents = False ' prevent macro changes retriggering this macro

    If Not Intersect(Target, Range("A2:A1000")) Is Nothing Then
        With Target(1, 2)
            .Value = Now
            .EntireColumn.AutoFit
        End With 

        Range("C" & Target.Row).Select ' go to row C

    End If

' second test plus actions

    If Not Intersect(Target, Range("G2:G1000")) Is Nothing Then

        LastRwA = Range("A" & Rows.Count).End(xlUp).Row ' get the last used row in column A
        Range("A" & LastRwA + 1).Select

    End If

    Application.EnableEvents = True 're-enable events

End Sub
Alternatively (and especially if you have data in rows below), you could replace both lines after the If...  in the second test with:
Target.Offset(1, -6).Select

Hope this makes sense and works for you.

Discuss

Discussion

p.s. "VBA question #2" is hardly the "descriptive" title I asked for! Note that your question title is used by others to find answers to similar  questions (and to give suggested questions when you enter a new question).
John_Ru (rep: 6102) Dec 18, '21 at 5:19 pm
Hi John,

I tried the code that you suggested. It runs after I entered data in column G. Once I hit eneter, it jumps down to line 1000 in column A. It does not go back up and select the last line that has data in it. What would be your work around?

Note: I am new to this forum, so forgive the mistakes made!
Zac (rep: 2) Dec 18, '21 at 6:11 pm
Zac, that will happen if there's something in cell A999 (which you should clear). I'd have spotted thst if you had uploaded your file with your question (it nearly always helps both of us) but it worked on a blank file. 

If not, you could replace both lines after the If...  in the second test with:
Target.Offset(1,-6).Select 
which moves the selection one row down and 6 rows to the left i.e. the next row down in A (which might not be empty)
John_Ru (rep: 6102) Dec 18, '21 at 6:50 pm
John,

Not sure how to share a file just yet.. Can you hep with that?
Zac (rep: 2) Dec 18, '21 at 7:08 pm
Also, there is nothing in line A999. Could it be because I have colored formats all the way to line A999?
 
Zac (rep: 2) Dec 18, '21 at 7:11 pm
Zac. To upload an Excel file (only), edit your original question (using Edit hyperlink below it) then use the Add Files... button which will appear below
John_Ru (rep: 6102) Dec 19, '21 at 2:35 am
John: file has been added

Thanks you!
Zac (rep: 2) Dec 20, '21 at 8:31 am
Thanks but it will ve several hours before I can check the file on my PC. 
John_Ru (rep: 6102) Dec 20, '21 at 8:45 am
Hi John:

Replacing both lines after the If...  in the second test with:
Target.Offset(1, -6).Select, worked.

Thanks for your assitance! 
Zac (rep: 2) Dec 20, '21 at 12:38 pm
Great, I've now added that to the end of my Answer. Please mark the Answer as Selected (for the guidance of others and my reputation!). Thanks Zac. 
John_Ru (rep: 6102) Dec 20, '21 at 1:46 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login