The Dynamic VLookup

[somnath6309]

The procedure which I am trying to design, searches the table array automatically and performs the vlookup function. Suppose a workbook have following sheets each have a range containing the “Total” key word :

BBSR

Guwahati

Siliguri

KolkataKG

KolkataHowrahKG

PatnaBo

Jamshedpur

Muzzafarpur

For example the sheet “BBSR” contains a range: $B$18:$E$18, where “Total” keyword resides at leftmost corner. In other sheets, there is same range but residing at different address i.e. the row number varies from sheet to sheet e.g. $B$20:$E$20, $B$26:$E$26.

I wanted to prepare such a Function Procedure that accepts a sheet name as its argument and then searches the range and performs the Vlookup operation. Here, I want to lookup Column No. 4 with respect to “Total” Key word in each sheet. The following is the code:

Function FINDROWNUM(sht As Worksheet) As Long

Dim Rw As Long

Rw = sht.Cells.Find(what:="Total", After:=Range("A1"), LookIn:=xlFormulas, Lookat:=xlPart,Searchorder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=False).Row

MsgBox Rw

FINDROWNUM = Rw

End Function


Function FINDCOLUMN(sht As Worksheet)

Dim col As Long

col = sht.Cells.Find(what:="Total", After:=Range("A1"), LookIn:=xlFormulas, Lookat:=xlPart, Searchorder:=xlByColumns, searchdirection:=xlPrevious, MatchCase:=False).Column

FINDCOLUMN = col

End Function

Function NEWVLOOKUP1(sht As Worksheet) As Long

Dim RowNum As Long

Dim ColNum As Long

Dim Rng As Range

RowNum = FINDROWNUM(sht)

ColNum = FINDCOLUMN(sht)

Set Rng = Range(Cells(RowNum, ColNum), Cells(RowNum, ColNum).Offset(0, 3))

NEWVLOOKUP1 = Application.WorksheetFunction.VLookup("Total", Rng.Address, 4, 0)

End Function

The function NEWVLOOKUP1 seems to create problem and hence is not working and showing #VALUE! Error. To test my code I introduced the following Sub Procedure that shows address of the Vlookup Range in “BBSR” Sheet :

Sub ShowAddress()

Dim RowNum As Long

Dim ColNum As Long

Dim Rng As Range

Dim Addr As String

Dim sht As Worksheet

Set sht = ActiveWorkbook.Worksheets("BBSR")

RowNum = FINDROWNUM(sht)

ColNum = FINDCOLUMN(sht)

Set Rng = Range(Cells(RowNum, ColNum), Cells(RowNum, ColNum).Offset(0, 3))

Addr = Rng.Address

MsgBox Addr

End Sub

And this time the Message box showed the address of Vlookup range $B$18:$E$18 ! and it seems that I am going in right direction.

Now, My question is What Modification is to be made in NEWVLOOKUP1 Function procedure so that it accepts a worksheet name of activeworkbook as its argument and then searches the lookup range and performs the vlookup function ?

For better understanding attaching the workbook where all sheets are present and the first sheet is the Report sheet.

 

[Marc L]

Hi,

you can use Address property like in this thread …
​