Can this function be made more efficient?

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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...

 

[Guest]

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!

 

[Guest]

Check out this link...

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

 

[Guest]

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.

 

[Alan Beban]

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

 

[Guest]

That's right.

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...

 

[Dana DeLouis]

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


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