FWIW, following is an abandoned attempt I once made to improve vlookup.
This may not be useful to OP but perhaps anyone may tweak it to make a
killer replacement - faster and more versatile. I have sheets with
hundreds of rows by hundreds of columns of vlookups and I bet that I'm
not alone in the crawling speed pain.
----------------------------------
Option Explicit
Dim gResult
Function MyVlookupAlpha(sStr As String, rangeWhere As Range, iColOffset
As Integer)
Dim rangeWhere2 As Range, c As Range
'NOTE! Only supply 1 column to the function - e.g. ("X",A1:A9,3), NOT
("X",A1:E9,3)
'Remember that the last arg (iColOffset) is 1-based, like idiotic
vlookup
Stop 'this line really helps debugging functions, vs. subs
'Set rangeWhere2 = rangeWhere.Columns(1)
For Each c In rangeWhere 'rangewhere2 doesn't work! JEEZ!
If c.Value = sStr Then
'Debug.Print c.Offset(0, iColOffset - 1).Value
MyVlookupAlpha = c.Offset(0, iColOffset - 1).Value
Exit Function
End If
Next c
MyVlookupAlpha = 0 'consider "" in another variation, etc.
End Function
Sub MySubVlookupAlpha(sStr As String, rangeWhere As Range, iColOffset As
Integer)
'sample data follows for MyVLOOKUPAlpha("Y1995M1",b2:d6,3)
'Dim sStr As String
'Dim rangeWhere As Range
'Dim iColOffset As Integer
'sStr = "Y1995M1"
'Set rangeWhere = ActiveSheet.Range("b2:d6")
'iColOffset = 3
Dim rangeWhere2 As Range, c As Range
Stop 'this line really helps debugging functions, vs. subs
Set rangeWhere2 = rangeWhere.Columns(1)
Set c = rangeWhere2.Find(sStr, LookIn:=xlValues, lookat:=xlWhole)
Stop
'For Each c In rangeWhere: Debug.Print c.Value: Next
If c Is Nothing Then
gResult = 0 'consider "" in another variation, etc.
Else
Debug.Print c.Offset(0, iColOffset).Value
gResult = c.Offset(0, iColOffset).Value
End If
Set c = rangeWhere2.Find(sStr, LookIn:=xlFormulas, lookat:=xlPart) '
to "reset"
End Sub
