VBA Code - VLOOKUP Formula with Dynamic Filename

[davidh120t]

I hope my favorite community can help me with a VBA problem that has been stumping me. I have a VBA macro where I want to insert a VLOOKUP formula into a cell. Sounds easy right? However, the workbook I am going to perform the lookup on is different that the one where the macro resides and I also want to use the name of that workbook (which I have captured and stored as a variable earlier) in the LOOKUP function. I have everything working until I get to the VLOOKUPand I cannot get the file name I stored to be used in the VLOOKUP function. I have placed my code below and highlighted the offending line of code. Thanks immensely in advance for any help! Also, I am trying to use a counter for the row number in that sheet which is going to be looked up.


Dim fd As FileDialog
Dim oFD As Variant
Dim fileName As String
Dim mywb As String
Dim subfile As String
Dim sheetname As String
Dim x As Integer
sheetname = "Sales"
mywb = ActiveWorkbook.Name
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.ButtonName = "Select"
.AllowMultiSelect = False
.Filters.Add "All Files", "*.*", 1
.Title = "Choose Webster Takeover File"
.InitialView = msoFileDialogViewDetails
.Show

For Each oFD In .SelectedItems
fileName = oFD
Next oFD
On Error GoTo 0
End With
Workbooks.Open (fileName)
subfile = ActiveWorkbook.Name
Workbooks(subfile).Activate
Sheets(sheetname).Select
x = 3
Cells(x, 1).Select
'Loop until a blank row is found
Do While Cells(x, 1).Value <> ""
Cells(x + 1, 1).Select
x = x + 1
Loop
Workbooks(mywb).Activate
Cells(1000, 2).Select
ActiveCell.Value = subfile
Cells(1, 3).Select
Application.CutCopyMode = False
Cells(1, 3).Select
ActiveCell.Formula = "=VLookup(A1,'(subfile)Sales'!$a$3:$b$x,2,FALSE)"
End Sub

 

[JoeMo]

Try replacing the bold line in your post with this one:

ActiveCell.Formula = "=VLookup(A1," & "[" & subfile & "]Sales!$a$3:$b$x,2,FALSE)"

 

[chicagocomputerclasses]

Since you are entering a formula in your worksheet, the correct syntax to refer to a different workbook is

'[Book1]Sheet1 235'!$A$1

so to construct that you would need:

"'[" & varableworkbookname & "]Sheet1 235'!$A$1"

 

[davidh120t]

Joe, thanks for the reply to my question but when I inserted the code as you suggested I received error message '1004' Application defined or object defined error. My code now looks like this:

Workbooks(mywb).Activate
Cells(1000, 2).Select
ActiveCell.Value = subfile
Cells(1, 3).Select
Application.CutCopyMode = False
Cells(1, 3).Select
ActiveCell.Formula = "=VLookup(A1," & " [" & subfile & "]Sales!!$a$3:$b$x,2,FALSE)"
End Sub

 

[davidh120t]

Chicago, thanks for the reply to my question but when I inserted the code as you suggested I received error message '1004' Application defined or object defined error. My code now looks like this:

Workbooks(mywb).Activate
Cells(1000, 2).Select
ActiveCell.Value = subfile
Cells(1, 3).Select
Application.CutCopyMode = False
Cells(1, 3).Select
ActiveCell.Formula = "=VLookup(A1," & " [" & subfile & "]Sales'!$a$3:$b$x,2,FALSE)"
End Sub

 

[chicagocomputerclasses]

First of all there are 2 errors in this one line
ActiveCell.Formula = "=VLookup(A1,"'[" & subfile & "]Sales'!$a$3:$b$" & x & ",2,FALSE)"

That being said it seems like you have a lot of other errors in there. For instance what is your while loop for?

I'd suggest providing a sample of your dataset and explain what you are trying to achieve based on that.

 

[JoeMo]

Joe, thanks for the reply to my question but when I inserted the code as you suggested I received error message '1004' Application defined or object defined error. My code now looks like this:

Workbooks(mywb).Activate
Cells(1000, 2).Select
ActiveCell.Value = subfile
Cells(1, 3).Select
Application.CutCopyMode = False
Cells(1, 3).Select
ActiveCell.Formula = "=VLookup(A1," & " [" & subfile & "]Sales!!$a$3:$b$x,2,FALSE)"
End Sub

Sorry, I missed a couple of apostrophes. Try this:

ActiveCell.Formula = "=VLookup(A1," & "'[" & subfile & "]Sales'!$a$3:$b$x,2,FALSE)"

 

[chicagocomputerclasses]

JoeMo

My understanding is that x in $b$x is a variable.

 

[JoeMo]

JoeMo

My understanding is that x in $b$x is a variable.

Good catch, I didn't look at the cell references. That would change it to this:

ActiveCell.Formula = "=VLookup(A1," & "'[" & subfile & "]Sales'!$a$3:$b$" & x & ",2,FALSE)"