Can this function be made more efficient?
 

In preparation for migrating data, I needed a way to lookup without the
lookup range being sorted. So, I wrote the following function - but it really
slows things down. It is used in a spreadsheet as follows:
=FINDINRANGE(B2,'other sheet'!B4:G199,6)
where the parameters are identical to VLOOKUP.

Code:

Public Function findinrange(lookitup As Range, rainge As Range, offsett As
Integer)

'Function findinrange works in a manner similar to LOOKUP, but it can find
'a value (or valyou) even if not sorted or if a different type (ie: numeric
vs. text)

hereitis = 0
valyou = lookitup.Value
If IsNumeric(valyou) Then valyou = Str$(valyou)
With rainge
rose = rainge.Rows.Count
ro = 1
Do Until ro = rose
lookhere = .Cells(ro, 1).Value
If IsNumeric(lookhere) Then lookhere = Str$(lookhere)
If InStr(1, lookhere, valyou) 0 Then
hereitis = ro
ro = rose
Else
ro = ro + 1
End If
Loop
If hereitis = 0 Then
findinrange = CVErr(xlErrNA)
Else
findinrange = .Cells(hereitis, offsett).Value
End If
End With
End Function
--
Adios,
Clay Harryman

Can this function be made more efficient?
 

First thing is declare all of your variables. Any variable not declared will
be of type variant which is the slowest type of variable...
--
HTH...

Jim Thomlinson


"Clayman" wrote:

In preparation for migrating data, I needed a way to lookup without the
lookup range being sorted. So, I wrote the following function - but it really
slows things down. It is used in a spreadsheet as follows:
=FINDINRANGE(B2,'other sheet'!B4:G199,6)
where the parameters are identical to VLOOKUP.

Code:

Public Function findinrange(lookitup As Range, rainge As Range, offsett As
Integer)

'Function findinrange works in a manner similar to LOOKUP, but it can find
'a value (or valyou) even if not sorted or if a different type (ie: numeric
vs. text)

hereitis = 0
valyou = lookitup.Value
If IsNumeric(valyou) Then valyou = Str$(valyou)
With rainge
rose = rainge.Rows.Count
ro = 1
Do Until ro = rose
lookhere = .Cells(ro, 1).Value
If IsNumeric(lookhere) Then lookhere = Str$(lookhere)
If InStr(1, lookhere, valyou) 0 Then
hereitis = ro
ro = rose
Else
ro = ro + 1
End If
Loop
If hereitis = 0 Then
findinrange = CVErr(xlErrNA)
Else
findinrange = .Cells(hereitis, offsett).Value
End If
End With
End Function
--
Adios,
Clay Harryman

Can this function be made more efficient?
 

Thank you.
This did speed things up somewhat. Here is the new code (since there were no
variant types, I had to re-arrange some of the code):

Public Function findinrange(lookitup As Range, rainge As Range, offsett As
Integer)

'Function findinrange works in a manner similar to LOOKUP, but it can find
'a value (or valyou) even if not sorted or if a different type (ie: numeric
vs. text)

Dim hereitis As Integer, rose As Integer, ro As Integer
Dim valyou As String, lookhere As String

hereitis = 0
If IsNumeric(lookitup.Value) Then
valyou = Str$(lookitup.Value)
Else
valyou = lookitup.Value
End If
With rainge
rose = .Rows.Count
ro = 1
Do Until ro = rose
If IsNumeric(.Cells(ro, 1).Value) Then
lookhere = Str$(.Cells(ro, 1).Value)
Else
lookhere = .Cells(ro, 1).Value
End If
If InStr(1, lookhere, valyou) 0 Then
hereitis = ro
ro = rose
Else
ro = ro + 1
End If
Loop
If hereitis = 0 Then
findinrange = CVErr(xlErrNA)
Else
findinrange = .Cells(hereitis, offsett).Value
End If
End With
End Function

--
Adios,
Clay Harryman


"Jim Thomlinson" wrote:

First thing is declare all of your variables. Any variable not declared will
be of type variant which is the slowest type of variable...
--
HTH...

Jim Thomlinson


"Clayman" wrote:

In preparation for migrating data, I needed a way to lookup without the
lookup range being sorted. So, I wrote the following function - but it really
slows things down. It is used in a spreadsheet as follows:
=FINDINRANGE(B2,'other sheet'!B4:G199,6)
where the parameters are identical to VLOOKUP.

Code:

Public Function findinrange(lookitup As Range, rainge As Range, offsett As
Integer)

'Function findinrange works in a manner similar to LOOKUP, but it can find
'a value (or valyou) even if not sorted or if a different type (ie: numeric
vs. text)

hereitis = 0
valyou = lookitup.Value
If IsNumeric(valyou) Then valyou = Str$(valyou)
With rainge
rose = rainge.Rows.Count
ro = 1
Do Until ro = rose
lookhere = .Cells(ro, 1).Value
If IsNumeric(lookhere) Then lookhere = Str$(lookhere)
If InStr(1, lookhere, valyou) 0 Then
hereitis = ro
ro = rose
Else
ro = ro + 1
End If
Loop
If hereitis = 0 Then
findinrange = CVErr(xlErrNA)
Else
findinrange = .Cells(hereitis, offsett).Value
End If
End With
End Function
--
Adios,
Clay Harryman

Can this function be made more efficient?
 

Another tip: Throw out "As Integer" and always use "As Long" (unless
required by a particular sub or function.) Why? Your row count can exceed
the limit of an integer and you will get an error. As a habit I use Long
everywhere.

"Clayman" wrote:

Thank you.
This did speed things up somewhat. Here is the new code (since there were no
variant types, I had to re-arrange some of the code):

Public Function findinrange(lookitup As Range, rainge As Range, offsett As
Integer)

'Function findinrange works in a manner similar to LOOKUP, but it can find
'a value (or valyou) even if not sorted or if a different type (ie: numeric
vs. text)

Dim hereitis As Integer, rose As Integer, ro As Integer
Dim valyou As String, lookhere As String

hereitis = 0
If IsNumeric(lookitup.Value) Then
valyou = Str$(lookitup.Value)
Else
valyou = lookitup.Value
End If
With rainge
rose = .Rows.Count
ro = 1
Do Until ro = rose
If IsNumeric(.Cells(ro, 1).Value) Then
lookhere = Str$(.Cells(ro, 1).Value)
Else
lookhere = .Cells(ro, 1).Value
End If
If InStr(1, lookhere, valyou) 0 Then
hereitis = ro
ro = rose
Else
ro = ro + 1
End If
Loop
If hereitis = 0 Then
findinrange = CVErr(xlErrNA)
Else
findinrange = .Cells(hereitis, offsett).Value
End If
End With
End Function

--
Adios,
Clay Harryman


"Jim Thomlinson" wrote:

First thing is declare all of your variables. Any variable not declared will
be of type variant which is the slowest type of variable...
--
HTH...

Jim Thomlinson


"Clayman" wrote:

In preparation for migrating data, I needed a way to lookup without the
lookup range being sorted. So, I wrote the following function - but it really
slows things down. It is used in a spreadsheet as follows:
=FINDINRANGE(B2,'other sheet'!B4:G199,6)
where the parameters are identical to VLOOKUP.

Code:

Public Function findinrange(lookitup As Range, rainge As Range, offsett As
Integer)

'Function findinrange works in a manner similar to LOOKUP, but it can find
'a value (or valyou) even if not sorted or if a different type (ie: numeric
vs. text)

hereitis = 0
valyou = lookitup.Value
If IsNumeric(valyou) Then valyou = Str$(valyou)
With rainge
rose = rainge.Rows.Count
ro = 1
Do Until ro = rose
lookhere = .Cells(ro, 1).Value
If IsNumeric(lookhere) Then lookhere = Str$(lookhere)
If InStr(1, lookhere, valyou) 0 Then
hereitis = ro
ro = rose
Else
ro = ro + 1
End If
Loop
If hereitis = 0 Then
findinrange = CVErr(xlErrNA)
Else
findinrange = .Cells(hereitis, offsett).Value
End If
End With
End Function
--
Adios,
Clay Harryman

Can this function be made more efficient?
 

Change your integer types to Long. Two reasons. One is that row number can
exceed 32k where integer ends. Secondly Long is faster than integer. Because
your system is 32 bit VBA has to do some fancy footwork to deal with what is
essentially a 16 bit number.
--
HTH...

Jim Thomlinson


"Clayman" wrote:

Thank you.
This did speed things up somewhat. Here is the new code (since there were no
variant types, I had to re-arrange some of the code):

Public Function findinrange(lookitup As Range, rainge As Range, offsett As
Integer)

'Function findinrange works in a manner similar to LOOKUP, but it can find
'a value (or valyou) even if not sorted or if a different type (ie: numeric
vs. text)

Dim hereitis As Integer, rose As Integer, ro As Integer
Dim valyou As String, lookhere As String

hereitis = 0
If IsNumeric(lookitup.Value) Then
valyou = Str$(lookitup.Value)
Else
valyou = lookitup.Value
End If
With rainge
rose = .Rows.Count
ro = 1
Do Until ro = rose
If IsNumeric(.Cells(ro, 1).Value) Then
lookhere = Str$(.Cells(ro, 1).Value)
Else
lookhere = .Cells(ro, 1).Value
End If
If InStr(1, lookhere, valyou) 0 Then
hereitis = ro
ro = rose
Else
ro = ro + 1
End If
Loop
If hereitis = 0 Then
findinrange = CVErr(xlErrNA)
Else
findinrange = .Cells(hereitis, offsett).Value
End If
End With
End Function

--
Adios,
Clay Harryman


"Jim Thomlinson" wrote:

First thing is declare all of your variables. Any variable not declared will
be of type variant which is the slowest type of variable...
--
HTH...

Jim Thomlinson


"Clayman" wrote:

In preparation for migrating data, I needed a way to lookup without the
lookup range being sorted. So, I wrote the following function - but it really
slows things down. It is used in a spreadsheet as follows:
=FINDINRANGE(B2,'other sheet'!B4:G199,6)
where the parameters are identical to VLOOKUP.

Code:

Public Function findinrange(lookitup As Range, rainge As Range, offsett As
Integer)

'Function findinrange works in a manner similar to LOOKUP, but it can find
'a value (or valyou) even if not sorted or if a different type (ie: numeric
vs. text)

hereitis = 0
valyou = lookitup.Value
If IsNumeric(valyou) Then valyou = Str$(valyou)
With rainge
rose = rainge.Rows.Count
ro = 1
Do Until ro = rose
lookhere = .Cells(ro, 1).Value
If IsNumeric(lookhere) Then lookhere = Str$(lookhere)
If InStr(1, lookhere, valyou) 0 Then
hereitis = ro
ro = rose
Else
ro = ro + 1
End If
Loop
If hereitis = 0 Then
findinrange = CVErr(xlErrNA)
Else
findinrange = .Cells(hereitis, offsett).Value
End If
End With
End Function
--
Adios,
Clay Harryman

Can this function be made more efficient?
 

I got used to sticking with Integer types back when memory was a serious
consideration. It's not that way anymore, is it? LOL

Thanks for the tip. I'll keep that in mind.

And, I didn't think that it wouldn't hold all the rows in the range...
--
Adios,
Clay Harryman


"Charlie" wrote:

Another tip: Throw out "As Integer" and always use "As Long" (unless
required by a particular sub or function.) Why? Your row count can exceed
the limit of an integer and you will get an error. As a habit I use Long
everywhere.

"Clayman" wrote:

Thank you.
This did speed things up somewhat. Here is the new code (since there were no
variant types, I had to re-arrange some of the code):

Public Function findinrange(lookitup As Range, rainge As Range, offsett As
Integer)

'Function findinrange works in a manner similar to LOOKUP, but it can find
'a value (or valyou) even if not sorted or if a different type (ie: numeric
vs. text)

Dim hereitis As Integer, rose As Integer, ro As Integer
Dim valyou As String, lookhere As String

hereitis = 0
If IsNumeric(lookitup.Value) Then
valyou = Str$(lookitup.Value)
Else
valyou = lookitup.Value
End If
With rainge
rose = .Rows.Count
ro = 1
Do Until ro = rose
If IsNumeric(.Cells(ro, 1).Value) Then
lookhere = Str$(.Cells(ro, 1).Value)
Else
lookhere = .Cells(ro, 1).Value
End If
If InStr(1, lookhere, valyou) 0 Then
hereitis = ro
ro = rose
Else
ro = ro + 1
End If
Loop
If hereitis = 0 Then
findinrange = CVErr(xlErrNA)
Else
findinrange = .Cells(hereitis, offsett).Value
End If
End With
End Function

--
Adios,
Clay Harryman


"Jim Thomlinson" wrote:

First thing is declare all of your variables. Any variable not declared will
be of type variant which is the slowest type of variable...
--
HTH...

Jim Thomlinson


"Clayman" wrote:

In preparation for migrating data, I needed a way to lookup without the
lookup range being sorted. So, I wrote the following function - but it really
slows things down. It is used in a spreadsheet as follows:
=FINDINRANGE(B2,'other sheet'!B4:G199,6)
where the parameters are identical to VLOOKUP.

Code:

Public Function findinrange(lookitup As Range, rainge As Range, offsett As
Integer)

'Function findinrange works in a manner similar to LOOKUP, but it can find
'a value (or valyou) even if not sorted or if a different type (ie: numeric
vs. text)

hereitis = 0
valyou = lookitup.Value
If IsNumeric(valyou) Then valyou = Str$(valyou)
With rainge
rose = rainge.Rows.Count
ro = 1
Do Until ro = rose
lookhere = .Cells(ro, 1).Value
If IsNumeric(lookhere) Then lookhere = Str$(lookhere)
If InStr(1, lookhere, valyou) 0 Then
hereitis = ro
ro = rose
Else
ro = ro + 1
End If
Loop
If hereitis = 0 Then
findinrange = CVErr(xlErrNA)
Else
findinrange = .Cells(hereitis, offsett).Value
End If
End With
End Function
--
Adios,
Clay Harryman

Can this function be made more efficient?
 

Jim and Charlie (and anyone else who desires to chime in)

While we're on the subject of declaring variables, is it possible to declare
a group of variables as a certain type?

I've tried:
DIM a, b, c As String
but that only declares "c" as String type. I'm assuming "a" and "b" are both
variants.

I'm trying to avoid entering:
DIM a As String, b As String, c As String

Thanks!
--
Adios,
Clay Harryman

Can this function be made more efficient?
 

Check out this link...

http://www.cpearson.com/excel/variables.htm
--
HTH...

Jim Thomlinson


"Clayman" wrote:

Jim and Charlie (and anyone else who desires to chime in)

While we're on the subject of declaring variables, is it possible to declare
a group of variables as a certain type?

I've tried:
DIM a, b, c As String
but that only declares "c" as String type. I'm assuming "a" and "b" are both
variants.

I'm trying to avoid entering:
DIM a As String, b As String, c As String

Thanks!
--
Adios,
Clay Harryman

Can this function be made more efficient?
 

Ah, yes. Mr. Pearson always has good information. I need to visit his site
more often.

Thanks for the link. That's a good habit to get into.
--
Adios,
Clay Harryman


"Jim Thomlinson" wrote:

Check out this link...

http://www.cpearson.com/excel/variables.htm
--
HTH...

Jim Thomlinson


"Clayman" wrote:

Jim and Charlie (and anyone else who desires to chime in)

While we're on the subject of declaring variables, is it possible to declare
a group of variables as a certain type?

I've tried:
DIM a, b, c As String
but that only declares "c" as String type. I'm assuming "a" and "b" are both
variants.

I'm trying to avoid entering:
DIM a As String, b As String, c As String

Thanks!
--
Adios,
Clay Harryman

Can this function be made more efficient?
 

Although Chip's sites are always worth visiting, in this case the
quicker answer to your post is "too, bad; you're stuck with what you're
trying to avoid."

Alan Beban

Clayman wrote:
Ah, yes. Mr. Pearson always has good information. I need to visit his site
more often.

Thanks for the link. That's a good habit to get into.

Can this function be made more efficient?
 

That's right. :P

So, I went into NotePad with
Dim VarA, VarB, VarC As String

Edit|Replace All ", " With " As String, "

Then copied and replaced. Now - this wouldn't have been worth the effort
with only three variables. I had about a dozen Strings and another dozen
Longs.

As far as putting each Dim on a different line - I'm not sure at this point.
I think I need to write a script to write my macros...
--
Adios,
Clay Harryman


"Alan Beban" wrote:

Although Chip's sites are always worth visiting, in this case the
quicker answer to your post is "too, bad; you're stuck with what you're
trying to avoid."

Alan Beban

Clayman wrote:
Ah, yes. Mr. Pearson always has good information. I need to visit his site
more often.

Thanks for the link. That's a good habit to get into.

Can this function be made more efficient?
 

Just 2 cents. Probably not faster, but I would use a "For Each" statement
instead of ro = ro+1.

For Each Cell In RangeE.Cells
'...
If InStr(1, lookhere, valyou) 0 Then
hereitis = Cell.Row
Exit For


I'm trying to avoid entering:
DIM a As String, b As String, c As String

Not sure if you are aware of it, but you might want to check out DefType
statement.
For example "DefStr."
However, I believe it is not used very often.

For example:

Option Explicit
DefStr S

Sub BadIdea()
Dim s1, s2 '<- These are all Strings now.

s1 = 3
s2 = 4
End Sub

As you can see, s1 & s2 appear to be numbers, but they are now strings.
(Which is what you want)
But...it's just a little hard to follow
--
HTH :)
Dana DeLouis


"Clayman" wrote in message
...
That's right. :P

So, I went into NotePad with
Dim VarA, VarB, VarC As String

Edit|Replace All ", " With " As String, "

Then copied and replaced. Now - this wouldn't have been worth the effort
with only three variables. I had about a dozen Strings and another dozen
Longs.

As far as putting each Dim on a different line - I'm not sure at this
point.
I think I need to write a script to write my macros...
--
Adios,
Clay Harryman


"Alan Beban" wrote:

Although Chip's sites are always worth visiting, in this case the
quicker answer to your post is "too, bad; you're stuck with what you're
trying to avoid."

Alan Beban

Clayman wrote:
Ah, yes. Mr. Pearson always has good information. I need to visit his
site
more often.

Thanks for the link. That's a good habit to get into.