speed question

[Gary Keramidas]

i had the following code in 2 sections, so it was looping through 10K+ rows twice. so, i put both finds in the same loop(below) and used a column offset to do the 2nd find. each range to search is a different sheet.

but this doesn't appear to be any faster than when i was looping through the 10k+ rows twice.

just wondering,


For Each cell In rng
On Error Resume Next
Set cval = .Find((cell.Value), LookIn:=xlValues, lookat:=xlWhole)
On Error GoTo 0
If Not cval Is Nothing Then
If Trim(ws2.Range("E" & cval.Row)) > "" Then
ws.Range("O" & cell.Row) = ws2.Range("E" & cval.Row) ' Plate
ws.Range("T" & cell.Row) = ws2.Range("F" & cval.Row) ' net wt
End If
End If

With ws3.Range("E2:E" & lastrow3)
On Error Resume Next
Set sModel = .Find((cell.Offset(0, -2).Value), LookIn:=xlValues, lookat:=xlWhole).Offset(0, 1)
On Error GoTo 0
If Not sModel Is Nothing Then
With rng2
Set plModel = .Find(ws.Range("O" & cell.Row) & sModel)
If Not plModel Is Nothing Then
ws.Range("P" & cell.Row) = ws3.Range("C" & plModel.Row) ' Strokes
ws.Range("Q" & cell.Row) = ws3.Range("J" & plModel.Row) ' Wt/Stroke
ws.Range("R" & cell.Row) = ws3.Range("H" & plModel.Row) ' Size
ws.Range("S" & cell.Row) = ws3.Range("I" & plModel.Row) ' Shape
End If
End With
End If
End With
Next

 

[Guest]

I have found referencing a cell by either a row or a column number is slow.
It is faster to use absolute referencing.

Rather than
ws.Range("O" & cell.Row) = ws2.Range("E" & cval.Row)
use
cell.offset(0,14) = cval.offset(0,4)

 

[Gary Keramidas]

thanks joel.

both ways take about 6 seconds on my pc.

 

[Guest]

6 seconds isn't bad. Usualy when people complain about spped problems it
take 5 minutes to 1/2 hour to complete. Consider yourself lucky.