Solved: Simple loop not working properly

[boller4prez]

I have created some excel vba code. I have data that may be in multiple cells of two sheets. It should go through all the cells comparing each indivdually trying to find data that matches with column A on sheet 2.

But it fails with error 400.

attach included




code:
Sub test()
Set s = Sheets("Sheet1")
Set r = Sheets("Sheet2")
Dim i
Dim j
i = 2
j = 2

s.Range("K1") = r.Range("G1")
s.Range("L1") = r.Range("H1")
s.Range("M1") = r.Range("I1")
s.Range("N1") = r.Range("J1")
s.Range("O1") = r.Range("K1")
Do Until s.Range("D" & i) = ""
If r.Range("A" & j) = s.Range("D" & i) Then
s.Range("K" & i) = r.Range("G" & j)
s.Range("L" & i) = r.Range("H" & j)
s.Range("M" & i) = r.Range("I" & j)
s.Range("N" & i) = r.Range("J" & j)
s.Range("O" & i) = r.Range("K" & j)
i = i + 1
Else
j = j + 1
End If
Loop
End Sub

[Sagy]

Your problem might be that you can loop until you "run out" of excel rows.

Once r.Range("A" & j) <> s.Range("D" & i) and s.Range("D" & i) <> ""
you just move the j index to the next row as a result you could run out of rows (65537) in Sheet2.

You might want to change the loop condition to
Do Until (s.Range("D" & i) = "") Or (r.Range("A" & j) = "")

[boller4prez]

Thx for your help. I knew it something stupid I missed.

In case anyone has a similar problem here is the final code:

Sub test()
Set s = Sheets("Sheet1")
Set r = Sheets("Sheet2")
Dim i
Dim j
i = 2
j = 2

s.Range("K1") = r.Range("G1")
s.Range("L1") = r.Range("H1")
s.Range("M1") = r.Range("I1")
s.Range("N1") = r.Range("J1")
s.Range("O1") = r.Range("K1")
Do Until (s.Range("D" & i) = "") Or (r.Range("A" & j) = "")
If r.Range("A" & j) = s.Range("D" & i) Then
s.Range("K" & i) = r.Range("G" & j)
s.Range("K" & i).NumberFormat = "m/d/yyyy"
s.Range("L" & i) = r.Range("H" & j)
s.Range("L" & i).NumberFormat = "m/d/yyyy"
s.Range("M" & i) = r.Range("I" & j)
s.Range("M" & i).NumberFormat = "m/d/yyyy"
s.Range("N" & i) = r.Range("J" & j)
s.Range("N" & i).NumberFormat = "m/d/yyyy"
s.Range("O" & i) = r.Range("K" & j)
s.Range("O" & i).NumberFormat = "m/d/yyyy"
i = i + 1
Else
j = j + 1
End If
Loop
End Sub

[CreganTur]

Welcome to the forum! It's always good to have new members.

When posting code, please wrap it in VBA tags (click the green VBA button). This will format the code according to VBIDE, which makes it much easier to use.

Also, if your issue is resolved, please mark it as solved by clicking on Thread tools at the top of the thread, and selecting "Mark as solved". It'll help keep the board clean.

Thanks :thumb

[boller4prez]

Done thanks for the helpful advice for a newbie.