if you have 50000 terms you are searching for, you might sort both your
search terms and the data being searched, then you can just advance each
listing to reflect more realistic possibilities rather than search the full
data list 50000 times. Just maintain a pointer in each list, if I am search
for B's I can quickly set my data list pointer to point to the first B and
disregard A's. The I continue to advance it until I either match or have
exceeded my search term. If match, do your work, if not, increment the
pointer in the seach terms list.

But find, in general, is pretty much the fastest way to search a list in
Excel VBA I would think.

--
Regards,
Tom Ogilvy


"Kent Schederin" wrote in message
...
Hi

I have written a function which i use in a loop to find a value from
Sheets1
in a table in sheet 2. The function returns the rownumber

The function looks like this:

strRabattKod = Worksheets(1).Range("C" & i).Value
lngRadnr = Hittarad(strRabattKod)

Function Hittarad(Rabattgrupp As String) As Long
Dim c As Object
With Sheets(2).Range("E3" & ":I" & lngLastRowAvtal)
Set c = .Find(Rabattgrupp, LookIn:=xlValues)
If Not c Is Nothing Then
Hittarad = c.Row
End If
End With
End Function

Sometimes i must loop 50000 times and it takes a whole lot of time. Today
almost 30 minutes

Is there a faster way to find this value than using the Find method?

When the function has found the value I use an If-structure to LookUp a
value in one column the row that was found.

With Sheets(2)
If .Cells(lngRadnr, 2) = "J" Then
x = .Cells(lngRadnr, 9)
ElseIf .Cells(lngRadnr, 4) = "" And .Cells(lngRadnr, 5) < "" Then
x = .Cells(lngRadnr, 6)
ElseIf .Cells(lngRadnr, 4) < "" And .Cells(lngRadnr, 5) = "" Then
x = .Cells(lngRadnr, 8)
End If

Then it calculates with the x variable

With Sheets(1)
.Range("G" & i) = Format(x / 1000, "0 %")
.Range("H" & i) = Cells(i, 2) * (1 - x / 1000)
End With


Thanks for any advice

KS