Max value in Do While counter
Dim i As Long
Dim j As Long
i = 1
j = 1
Do While Not (IsEmpty(Cells(i, 2)))
Do While Not (IsEmpty(Cells(j, 6)))
If Cells(i, 2) = Cells(j, 6) And _
Cells(i, 3) = Cells(j, 7) _
Then Cells(j, 8) = "1"
j = j + 1
Loop
j = 1
i = i + 1
Loop
You want to put a 1 in any row j (column H) where the values in columns F
and G match the values in Columns B and C respectively with the B and C
values being in row i.
this would require 54000 x 54000 comparisons. I your description, however,
you sound like you only want a 1 if all on the same row, B = F and C = G.
If so, you don't need a nested loop
Dim i As Long
i = 1
Do While Not (IsEmpty(Cells(i, 2)))
and Not (IsEmpty(Cells(j, 6)))
If Cells(i, 2) = Cells(i, 6) And _
Cells(i, 3) = Cells(i, 7) _
Then Cells(i, 8) = "1"
i = i + 1
Loop
If you post back with exactly what you want to check and do, then I am sure
we can suggest a faster way.
--
Regards,
Tom Ogilvy
ChrisG wrote in message
...
I get this code to work for small lists, however, the
real test is on almost 54,000 records and the code
crashes when j = 32,767. In a nutshell Col B and Col C
have values and are being compared against Col F and Col
G (If B=F AND C=G, then H=1)
Dim i As Integer
Dim j As Integer
i = 1
j = 1
Do While Not (IsEmpty(Cells(i, 2)))
i = i + 1
Do While Not (IsEmpty(Cells(j, 6)))
j = j + 1
If ((Cells(i, 2) = Cells(j, 6)) And (Cells(i, 3)
= Cells(j, 7))) Then Cells(j, 8) = "1"
Loop
j = 1
Loop
|