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