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

Use Dynamic Column for vLookUp in VBA

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I am trying to run below mentioned vLookUp in my data.
I want help in deciding how do use dynamic column in my formula.

For example, U2 will be replaced by a defined Column and row 2.

Also, U:U will be replaced by defined column.

I do not know how t use defined column references in vLookUp formula.
Can anyone please help me in this.

"=VLOOKUP(U2,'Previous Data'!U:U,1,0)"
 
I just used code I found on source site written in Macro & modified into Sub to store as Variable. Alternatively you could have a few inputboxes run & store values from them

Code: 
Sub DynamicCopy()

'Source: http://www.ozgrid.com/forum/showthread.php?t=55176

Dim MyCol, MyRow As String

MyCol = Split(ActiveCell.Address, "$")(1) 'For Column Number
MyRow = Split(ActiveCell.Address, "$")(2) 'For Row Number

ActiveCell.Formula = _
"=VLOOKUP(" & MyCol & MyRow & ",'Previous Data'!" & MyCol & ":" & MyCol & ",1,0)"

End Sub
 
By the way if you want to use it with a range then you could use this
Code: 
Sub RangeColRow()

Dim Rng, ST_Col, END_Col, ST_Row, END_Row As String

Rng = Range("VME2345:VMF4546").Address
ST_Col = Split(Rng, "$")(1) 'VME
ST_Row = Left(Split(Rng, "$")(2), Len(Split(Rng, "$")(2)) - 1) '2345
END_Col = Split(Rng, "$")(3) 'VMF
END_Row = Split(Rng, "$")(4) '4546

End Sub
 
Hi @chirayu , thanks for the help. I am using below mentioned code. It's working perfectly. Good night. :)


I just used code I found on source site written in Macro & modified into Sub to store as Variable. Alternatively you could have a few inputboxes run & store values from them
Click to expand...
 
Hi,

I am trying to populate vLookUp from another file with below mentioned formula.

"'"=VLOOKUP(" & MyCol & MyRow & ",'Previous Data'!" & MyCol & ":" & MyCol & ",1,0)""

However I am not able to define file name in the above formula. I tried various combinations but it's not working.

Can anyone please tell me how to define file name in this formula.

Kindly note, my file is defined as File1 in the macro.
 
Hi,

My problem is I am not able to define "'[Aug 15_For_Reference.xls]" this part of the formula.

I have defined "'[Aug 15_For_Reference.xls]" as File1 and I am trying to replace "'[Aug 15_For_Reference.xls]" with File1 in the below mentioned formula.

"ActiveCell.Formula = _
"=VLOOKUP(" & MyCol & MyRow & ",'[Aug 15_For_Reference.xls]PrISTOCK'!" & MyCol & ":" & MyCol & ",1,0)"
 
Hi,

It is working now. I stored file name as string and used it in the formula as mentioned below.

Will be thankful if anyone can suggest better option to do this.

Thanks. :)

Code: 
Const File3Name As String = File3.Name
    File3Name = File3.Name
   
    ActiveCell.Formula = _
    "=VLOOKUP(" & MyCol & MyRow & ",'[" & File3Name & "]ABC'!" & MyCol & ":" & MyCol & ",1,0)"
 
Bonjour Monsieur @Marc L ! Thanks a lot for the help. Have a nice day ahead. :)

Rather than using file3.name I was using weird combinations which caused bug.
 
Hi,

Stuck with another problem while using below mentioned code.

How do I replace "1" in the formula with a variable. Is there a way to do it.

I have defined LR = 5 in my code however I am not replace "1" with LR in the below code.

The reason, I do not know if it will be 1, 2 or 3 so I have defined the Column No as long and trying to use it in the code.

Can anyone please help me in this.

"=VLOOKUP(" & MyCol & MyRow & ",'[Aug 15_For_Reference.xls]PrISTOCK'!" & MyCol & ":" & MyCol & ",1,0)"
 
@ ThrottleWorks

Just noticed that you are struggling with the Vlookup from three weeks. Will you pls upload the sample data with desired output so that here, we may suggest a more robust way or streamline your way!!
 
Hi @chirayu , thanks for the help.

Hi @Deepak Sir, thanks for the help, please find attached file for your reference.

Kindly help if you get time.

I am trying to find "Name" column (D) from Output file to input file.

Then I try to find out "East" in Input file.

I am trying to make the entire formula dynamic.

"=VLOOKUP(D2,[Input.xlsb]Sheet1!$A:$C,3,0)"

Right now I am not able to understand how to define 3 in this formula.
 

Attachments

  • Input.xlsb
    8 KB · Views: 4
  • output.xlsb
    7.6 KB · Views: 3
Last edited:
Why don't you use a Vlookup match to match the headers - east/west/north etc

=VLOOKUP($D2,[Input.xlsb]Sheet1!$A:$E,MATCH(E$1,[Input.xlsb]Sheet1!$A$1:$E$1,0),0)

That way no need to find column number
 
Last edited:
You must log in or register to reply here.
Back
Top