Optimize Slow function
 

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

Optimize Slow function
 

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

Optimize Slow function
 

Thanks TomI
I was thinking that maybe the function VLOOKUP could be faster.
Or could it be my IF-structure that could be written in a more efffcient
way. Though, I can´t see that there is any other way to write this.

KS

"Tom Ogilvy" wrote in message
...
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

Optimize Slow function
 

I would think Match vice Vlookup if you want to get the location of the
cell. While I haven't tried it, I would think Find would be faster. I
would expect the consumption of time would be in the looking and finding,
not in the working with the results. I think you will find restructuring
your data will be beneficial as long as you code to take advantage of that.
If you sorted your data, then perhaps match would be faster if you used an
inexact match rather than demand an exact match. Match could then probably
do a binary search - while Find is generalized and probably can't assume
anything about the data.


--
Regards,
Tom Ogilvy

"Kent Schederin" wrote in message
...

Thanks TomI
I was thinking that maybe the function VLOOKUP could be faster.
Or could it be my IF-structure that could be written in a more efffcient
way. Though, I can´t see that there is any other way to write this.

KS

"Tom Ogilvy" wrote in message
...
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