Iterate if statement in vba

Question

I am new to VB and facing some issues to iterate through. below is my code and i want to iterate the if statement more than once.

Sub Refresh_Data()

On Error Resume Next
A = Worksheets("DATA").Cells(Rows.Count, 4).End(xlUp).Row
    Dim x As String
     
        x = 9550
   For i = 1 To A
   
   If Worksheets("DATA").Cells(i, 1).Value = x Then
      
        Worksheets("DATA").Rows(i).Copy 
        Worksheets(x).Activate 
        B = Worksheets(x).Cells(Rows.Count, 4).End(xlUp).Row 
        Worksheets(x).Cells(B + 1, 1).Select 
        ActiveSheet.Paste  
        Worksheets("DATA").Activate 
        x = x + 50

    End If
 Next
Application.CutCopyMode = False

ThisWorkbook.Worksheets("DATA").Cells(1, 1).Select

End Sub

If you are new to VBA -- don't use `On Error Resume Next` ! It has *some* valid uses, but you are using it as `On Error Hide Bug`. Also -- if you are new to VBA you really should use `Option Explicit`. Do it once and for all be enabling `Require Variable Declarations` in the VBA editor options. Finally, read and take to heart [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248). Those three changes will significantly help your code (though maybe not answer this particular question). — John Coleman, Jul 27 '20 at 12:17

Dominique · Answer 1 · 2020-07-27T13:16:38.160

You are clearly making some rookie mistakes in your code, let me make some first corrections, and from there please tell us if you still have problems and in case yes, which ones:

Sub Refresh_Data()

' On Error Resume Next (do not use this, it is masking errors instead of solving them)
Dim A As Long
Dim B As Long ' not only A, also B
Dim x As Long ' x is not a string, but a number
Dim i As Long ' you forgot about i

A = Worksheets("DATA").Cells(Rows.Count, 4).End(xlUp).Row
     
x = 9550
For i = 1 To A
   
  If Worksheets("DATA").Cells(i, 1).Value = x Then
      
    Worksheets("DATA").Rows(i).Copy
    Worksheets(x).Activate
    B = Worksheets(x).Cells(Rows.Count, 4).End(xlUp).Row
    Worksheets(x).Cells(B + 1, 1).Paste ' you can paste here directly, no reason to select first.
    Worksheets("DATA").Activate
    x = x + 50

  End If
Next
Application.CutCopyMode = False

ThisWorkbook.Worksheets("DATA").Cells(1, 1).Select

End Sub

Thanks in advance for all the answers. johncoleman, yes am new to vba and sure would learn thing you mentioned. @dominique, Thanks for the suggestions, i have kept 'x' as string bcs was using that value as worksheet name. ..skay.. — user3875524, Jul 29 '20 at 05:46

Iterate if statement in vba

1 Answers1