Improving the speed of FOR/NEXT statements

[andycharger]

Hi,

I wonder if you can help guys and gals.....

I have 2 spreadsheets. One has about 1000 rows, one has about 28,00
rows.

What I am doing is using a FOR statement to get the next row in th
smaller spreadsheet and then looping through the 28,000 records in th
other spreadsheet to find the matching row using another FOR statemen
that I have nested.
When it is found, it does some changes then exits the inner FO
statement.

Problem is, it takes FOR EVER! (pardon the pun!)

Any ideas as to how I can improve the speed? Here is a scaled dow
version of what I am doing!!!

For RowDates = Range("A1").End(xlDown).Row To 2 Step -1

strRef = Cells(RowDates, "A").Value
StrYes = Cells(RowDates, "B").value

For RDates = Range("A1").End(xlDown).Row To 2 Step -1
if Cells(Rdates, "A").Value = strRef then
' do something

exit for
else

end if
Next

Next



Help!!!

 

[Tom Ogilvy]

Dim RowDates as Range, RDates as Range
Dim rng1 as Range, res as Variant
Dim cell as Range
Dim StrYes as String
With Worksheets("Sheet1")
set RowDates = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with
With Worksheets("Sheet2")
set RDates = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with

for each cell in RowDates
StrYes = cellOffset(0,1).Value
res = application.Match(clng(cell),RDates,0)
if not iserror(res) then
set rng1 = rDates(res)
' use rng1 to process row
end if
Next

 

[Keith Willshaw]

Hi,

I wonder if you can help guys and gals.....

I have 2 spreadsheets. One has about 1000 rows, one has about 28,000
rows.

What I am doing is using a FOR statement to get the next row in the
smaller spreadsheet and then looping through the 28,000 records in the
other spreadsheet to find the matching row using another FOR statement
that I have nested.
When it is found, it does some changes then exits the inner FOR
statement.

Problem is, it takes FOR EVER! (pardon the pun!)

Of course it does since you are making 28 million reads of the a value !

Any ideas as to how I can improve the speed? Here is a scaled down
version of what I am doing!!!

For RowDates = Range("A1").End(xlDown).Row To 2 Step -1

strRef = Cells(RowDates, "A").Value
StrYes = Cells(RowDates, "B").value

For RDates = Range("A1").End(xlDown).Row To 2 Step -1
if Cells(Rdates, "A").Value = strRef then
' do something

exit for
else

end if
Next

Next

Loop through the first one and then use the Excel find function in the
second

Why reinvent the wheel

Dim Searcht as Worksheet
Dim Searchrange as range
Dim Foundrange As Range

Set Searchsht = Worksheets ("YourSheet")

Set Searchrange = Searchsheet.Columns(1)
With Searchrange

For RowDates = Range("A1").End(xlDown).Row To 2 Step -1

strRef = Cells(RowDates, "A").Value
StrYes = Cells(RowDates, "B").value

set Foundrange = nothing
Set Foundrange = .Find( strRef, LookIn:=xlValues)

If Not Foundrange Is Nothing Then
'Do Your STuff
Endif
Next RowDates

End With

Keith

 

[andycharger]

Keith, using your example, how do I select the row that it finds th
value in?
For example using:

Set fr = Searchrange.Find("GUHX01HP03", LookIn:=xlValues)

will just give me that cell value whereas I need the entire row so
can edit bits on it.

Any ideas?

Thanks again

And