Looping my IF statement (VBA)

[excelerino]

Hi guys,

I have this code (part of a larger macro, so ignore the lack of opening/end statements):

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheet1
Set ws2 = Sheet9
With ws1
ws1LRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
With ws2
ws2LRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With


If ws2LRow Like ws1LRow Then
End If

If Not ws2LRow Like ws1LRow Then
Sheet9.Rows(20).EntireRow.Copy
Sheet9.Activate
ActiveSheet.Range("A4000").End(xlUp).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
End If

It checks if the last rows in 2 sheets are the same, if not it copies a row to the second sheet (making them equal again, theoretically). It works perfectly, but only if 1 row is added! If 2 or more rows are added in the first sheet, it will only add 1 row and then end the process. Of course I can run it again (and again with multiple rows) until they are equal, but having a loop would obviously be more useful. I just don't quite know the syntax! So if anyone could help, that would be much appreciated

 

[Joe4]

Without seeing what your data looks like, what your expected results looks like, and your full VBA code, it is tough to give you a specific answer.
But perhaps what you are looking for is a DO ... WHILE loop in VBA, that it keeps running as long as one value (one sheet's last row calculation) is less than the other.
See here: Excel VBA Loop - EASY Excel Macros

 

[excelerino]

Thanks Joe.

Basically I'd need the first part, which checks if the rows are equal and exits if they are

If ws2LRow Like ws1LRow Then
End If

To run again, after the second part. So that, if the two last rows are now equal, we exit, but if after adding a row, they are still different, we run the second part again

If Not ws2LRow Like ws1LRow Then
Sheet9.Rows(20).EntireRow.Copy
Sheet9.Activate
ActiveSheet.Range("A4000").End(xlUp).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
End If

So the two if statements should continue until the first statement is TRUE, or rather, the IF NOT statement should not end the process, but rather lead to the first IF statement again, which could then exit if true.

 

[Joe4]

Since ws1LRow and ws2LRow are numbers, just use equality instead of like, i.e.

If ws2LRow = ws1LRow Then

For a loop, it would just be structured something like:

Do Until ws2LRow = ws1LRow
'   your code here

'   then add one to one row reference
    wsL2Row = wsL2Row + 1
Loop

I am not sure which row reference you need to add 1 to.
Also, if you find it is doing one row short, you may need to change this line:

Do Until ws2LRow = ws1LRow

to

Do Until ws2LRow > ws1LRow

Also, what is the point of setting ws1 and ws2 to Sheet1 and Sheet9, if you continue to use Sheet1 and Sheet9 referencing?
i.e., change this:

[COLOR=#333333]Sheet9.Rows(20).EntireRow.Copy[/COLOR]
[COLOR=#333333]Sheet9.Activate[/COLOR]

to this:

ws2[COLOR=#333333].Rows(20).EntireRow.Copy[/COLOR]
[COLOR=#333333]ws2.Activate[/COLOR]

The point is to try to be consistent. If you are going to use it, use it consistently throughout all your code, instead of mixing the two types of references.

 

[excelerino]

Thank you very much. I hadn't incremented the last row by 1 when I tried the Do Until, that is exactly what I needed. Also thanks on the other critique, still learning, so much appreciated!

 

[Joe4]

You are welcome!
Looks like you are well on your way!