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 loop

0

I have entered this code from your Macros class 3.  the debug stops at the

" for i = 2 to a"

Private Sub CommandButton6_Click()
Dim i As Long
a = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp)
For i = 2 To a
   If Worksheets("sheet1").Cells(i, 3).Value = "north" Then
   
      Worksheets("sheet1").Rows(i).Copy
      Worksheets("sheet2").Activate
      b = Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
      Worksheets("sheet2").Cells(b + 1, 1).Select
      ActiveSheet.Paste
      Worksheets("sheet1").Activate
   End If
   Exit For
Next i
Application.CutCopyMode = False
ThisWorkbook.Worksheets("sheet1").Cells(1, 1).Select
End Sub


Thanks for your help  carroll

Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

Please surround your code with CODE tags in the future; I did it for you this time :)
don (rep: 1989) Jan 25, '19 at 9:51 pm
OK  THANKS
carroll (rep: 16) Jan 27, '19 at 1:51 am
Add to Discussion

Answers

0
Selected Answer

Hi Carroll, I think that you need to change line 3 in your macro from this:

a = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp)

to this:

a = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Currently, that line of code in your macro doesn't return a number value, and that is what is causing the issue with the code that is for the start of the loop.

Discuss

Discussion

thanks for the answer Don
carroll (rep: 16) Jan 27, '19 at 1:52 am
You're welcome! Glad to help :)
don (rep: 1989) Jan 27, '19 at 11:33 am
Add to Discussion


Answer the Question

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