• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Vlookup Macro

Mofi454

New Member
Hi I am looking to create a dynamic vlookup using a macro.
I need a vlookup formula that looks up the taxcode in column A of the sheet labelled Manual Adjustment
from the Tax Code of the sheet labelled Data.

In the Data tab the number of rows can be both more or less than shown in the attached.

For the Output tax it will always start on row 3 but if this dataset increases then the input tax may start at row 10 (as per the attached) but may also start in say row 15.

I used the following code to find the Input Tax to determine the start point. But I need a way of passing the variable startCell to my Vlookup.
I tried using End (xlDown) but to no avail

>>> use code - tags <<<
Code:
Private Sub fd()
Dim ws As Worksheet, findText As Range, startCell As String
Set ws = Sheets("Data")
ws.Select
Set findText = Cells.Find(What:="Input Tax:*")
startCell = findText.Offset(2, 2).Address
'MsgBox (startCell)
End Sub

Any help would be very much appreciated

Thanks

Attachments
 

Attachments

  • Chandoo VLookup Question.xlsx
    14.5 KB · Views: 7
Last edited by a moderator:
Hi,​
first you must edit your post and use the code tags via the 3 dots icon …​
If you enter a VLOOKUP formula in a cell, what should it be ?​
 
Hi Marc, Sorry I didn't understand the first line.
In terms of my vlookup it should read as per the following
VLOOKUP('Manual Adjustment'!$A19,Data!$C$11:$G$16,2,0)
The table_array is the part that should be dynamic, that is both the start and end may b begin at the different rows.
The start is determined by startCell from the fd above
Thanks
 
Mofi454
Your Sorry I didn't understand the first line.
You should reread Forum Rules
as well as this
After those, You would remember those which You have read a long time ago.
 
You don't need vba for this, a formula such as:
=VLOOKUP(A3,OFFSET(Data!$C$1:$I$300,MATCH("Input Tax:*",Data!A:A,0)+1,0),7,0)
will do this, where A3 is the tax code you're looking up, the 7 is looking up the 7th column (Tax acct).
The 300 is the likely largest number of rows the table you're looking up is going to be, which you need to adjust yourself..
 
Back
Top