VLookup Macro

[sn152]

Hi All,

I am trying to do vlookup using vba code. I have 2 workbooks namely, Vlookup and Source. I want the planned start date in the "Source" workbook to be shown in "Vlookup" workbook in column "D" for each names.
Likewise, I want the Actual Start date from "Source" workbook to be shown in "Vlookup" workbook in column "E" for each names. i tried to record this. But each time the name of the source file changes. So iam getting an error.


Please help me with this.


Thanks in advance!

 

[Luke M]

No need to do this via VB...formula would be:
=VLOOKUP($A1,'C:\My Documents\[Source.xlsx]Sheet1'!$A$2:$H$13,6,)
and
=VLOOKUP($A1,'C:\My Documents\[Source.xlsx]Sheet1'!$A$2:$H$13,8,)

In VB language, would be:

Range("D1:D6").Formula = _
"=VLOOKUP($A1,'C:\My Documents\[Source.xlsx]Sheet1'!$A$2:$H$13,6,)"
Range("E1:E6").Formula = _
"=VLOOKUP($A1,'C:\My Documents\[Source.xlsx]Sheet1'!$A$2:$H$13,8,)"

Now, you alluded to the source file's name changing, but didn't give any more information about that. If the file name does change, you'll need to tell us how the code is supposed to know where to look.

 

[sn152]

Hi Luke,

The source file's name changes. Can I have an option to update the file name in a cell in sheet2. So that every time when the file name changes i can update the same in sheet2. And when I run the macro it should open the source workbook according to the name specified in the sheet2.

 

[Luke M]

An example of how to do that.

Sub MakeFormulas()
Dim fPath       As String
Dim fName       As String

'Define where the source is
fPath = ThisWorkbook.Path & "\"
fName = Worksheets("Sheet2").Range("A1").Value

'Open the source
Workbooks.Open (fPath & fName)

'Since the source is now open, don't need full path in
'our formulas
Range("D1:D6").Formula = _
"=VLOOKUP($A1,'[" & fName & "]Sheet1'!$A$2:$H$13,6,)"
Range("E1:E6").Formula = _
"=VLOOKUP($A1,'[" & fName & "]Sheet1'!$A$2:$H$13,8,)"


End Sub

 

[sn152]

Hi Luke,

thanks for replying. i tried the above code. But I shows N/A in the columns D and E after running this code.

 

[Luke M]

Have you verified that the value in col A exists in col A, Sheet1, or the Source workbook? Does the formula look correct? Is the file extension included?

 

[sn152]

Yes I checked that and the formula looks correct. But Iam getting the error ("N/A")

 

[sn152]

Hi Luke,

I have attached the updated source file and the VLookup file. Now when I enter the source file path in sheet 2 of workbook "Vlookup", and then if I run the macro it should open the file and put the dates in Sheet1 (Columns "D" and "E") of workbook "Vlookup". The dates in the source file will be available in sheet 1(column F and H) of that workbook. Can you please help me?

Thanks!

 

[NARAYANK991]

Hi ,

See your file now.

Narayan

 

[sn152]

This works fine... Thank you so much Narayan....