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

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

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

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

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

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

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

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
>

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

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.