Vlookup using a variable for the workbook name

P

Phil

Hi Guys,

Not wishing to bother anyone here, I've been struggling with this all
weekend on my own, and got nowhere, so I'm turning to you all for
help!

Here's the problem:

1. I have a workbook open that needs to gather data from a series of
other workbooks - one at a time, so the macro needs to:

a) Let the user browse to the workbook to be opened
 
P

Phil

Please disregard the last bit - I hit the wrong button!!!!!

This is what I'm trying to say....

Hi Guys,

Not wishing to bother anyone here, I've been struggling with this all
weekend on my own, and got nowhere, so I'm turning to you all for
help!
Click to expand...

Here's the problem:1. I have a workbook open that needs to gather data from a series of
other workbooks - one at a time, and each decided by the user.......so
the macro needs to:

   a) Let the user browse to the first workbook to be opened
b) Get the filename of that workbook
c) Concatenate two of the columns in that book
d) Define a range for a VLOOKUP in the already open book
e) Create said formula in the already open book

Currently I'm doing it using the following code, which posts the
worbook name in cell B65535 so I can use it as a variable (there's
probably a better way, but I can't think of one). All works fine until
I use that variable in the VLOOKUP formula and it all goes haywire.
I'm not sure if it's a syntax thing - although I've tried all sorsts
of ways - or something else. Here's the code.....

Sub Add_New_Miles()

Dim iLastRow0 As Long
Dim iLastRow1 As Long
Dim iLastRow2 As Long
Dim Bookname As String



FName = Application.GetOpenFilename()
If FName <> False Then
Workbooks.Open Filename:=FName
End If

Range("B65535").Select
ActiveCell.FormulaR1C1 = _

"=MID(CELL(""filename"",R[-65534]C),FIND(""["",CELL(""filename"",R[-65534]C)),FIND(""]"",CELL(""filename"",R[-65534]C))-
FIND(""["",CELL(""filename"",R[-65534]C)))& ""]"""

' This should give a result in the form [filename] to assign to the
variable below....

Bookname = Range("B65535").Value


' The next bit calculates the number of rows in the dataset....

iLastRow0 = Cells(Rows.Count, "A").End(xlUp).Row

' Now it concatenates two columns to use in the lookup later.....

Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & iLastRow0),
Type:=xlFillDefault
Range("F2:F" & iLastRow0).Select


' Now a range name ("newmiles") for the lookup is defined...

Range("F2:N" & iLastRow0).Name = "newmiles"

' Next, switch to other book and plant the VLOOKUP formula, which is
meant to look at two values in this spreadsheet and compare them with
the concatenated ones in the other (which works fine when doing this
manually).......This is the bit that's going wrong!!!!!!

Windows("members_cleaned.xls").Activate
Range("AB2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-23]&RC[-22],'" & Bookname &
"'!newmiles,9,FALSE)"


However I word the formula, it comes up with Runtime error 1004 ...
Application- defined or object-defined error. Not being much cop at
this, I haven't a clue what it means..... except that when I practiced
the macro without a variable for the workbook name it was fine. Also,
I've tested it by printing the current variable into a cell an that's
correct....so what could it be?

Sorry the message is verbose - I just wanted to make it as clear as I
can. Can anyone help?????????

Phil
 
D

Dave Peterson

You can use object variables to represent the workbook that's opened and another
to represent the worksheet that contains the values to retrieve.

As well as another for the worksheet that gets the =vlookup() formula. I'm
guessing that this is the worksheet that's active when you start (change it if I
guessed wrong).

This is untested, but did compile:

Option Explicit
Sub Add_New_Miles2()

Dim CurWks As Worksheet
Dim FName As Variant 'could be boolean
Dim iLastRow As Long
Dim WkbkToOpen As Workbook
Dim wks As Worksheet
Dim LookUpRng As Range

FName = Application.GetOpenFilename()
If FName = False Then
MsgBox "Quitting!"
Exit Sub
End If

'what worksheet gets the =vlookup() formula?
Set CurWks = ActiveSheet

Set WkbkToOpen = Workbooks.Open(Filename:=FName)
Set wks = WkbkToOpen.Worksheets(1) '.worksheets("SomeSheetNameHere") '???

With wks
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("F1").EntireColumn.Insert
.Range("F2:F" & iLastRow).FormulaR1C1 = "=RC[-2]&RC[-1]"
.Parent.Save 'save the workbook with that concatenated cell???
Set LookUpRng = .Range("F2:N" & iLastRow)
End With

With CurWks
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("AB2:AB" & iLastRow).FormulaR1C1 _
= "=VLOOKUP(RC[-23]&RC[-22]," _
& LookUpRng.Address(external:=True) _
& ",9,FALSE)"
End With

End Sub
Please disregard the last bit - I hit the wrong button!!!!!

This is what I'm trying to say....

Hi Guys,

Not wishing to bother anyone here, I've been struggling with this all
weekend on my own, and got nowhere, so I'm turning to you all for
help!
Click to expand...

Here's the problem:1. I have a workbook open that needs to gather data from a series of
other workbooks - one at a time, and each decided by the user.......so
the macro needs to:

a) Let the user browse to the first workbook to be opened
b) Get the filename of that workbook
c) Concatenate two of the columns in that book
d) Define a range for a VLOOKUP in the already open book
e) Create said formula in the already open book

Currently I'm doing it using the following code, which posts the
worbook name in cell B65535 so I can use it as a variable (there's
probably a better way, but I can't think of one). All works fine until
I use that variable in the VLOOKUP formula and it all goes haywire.
I'm not sure if it's a syntax thing - although I've tried all sorsts
of ways - or something else. Here's the code.....

Sub Add_New_Miles()

Dim iLastRow0 As Long
Dim iLastRow1 As Long
Dim iLastRow2 As Long
Dim Bookname As String

FName = Application.GetOpenFilename()
If FName <> False Then
Workbooks.Open Filename:=FName
End If

Range("B65535").Select
ActiveCell.FormulaR1C1 = _

"=MID(CELL(""filename"",R[-65534]C),FIND(""["",CELL(""filename"",R[-65534]C)),FIND(""]"",CELL(""filename"",R[-65534]C))-
FIND(""["",CELL(""filename"",R[-65534]C)))& ""]"""

' This should give a result in the form [filename] to assign to the
variable below....

Bookname = Range("B65535").Value

' The next bit calculates the number of rows in the dataset....

iLastRow0 = Cells(Rows.Count, "A").End(xlUp).Row

' Now it concatenates two columns to use in the lookup later.....

Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & iLastRow0),
Type:=xlFillDefault
Range("F2:F" & iLastRow0).Select

' Now a range name ("newmiles") for the lookup is defined...

Range("F2:N" & iLastRow0).Name = "newmiles"

' Next, switch to other book and plant the VLOOKUP formula, which is
meant to look at two values in this spreadsheet and compare them with
the concatenated ones in the other (which works fine when doing this
manually).......This is the bit that's going wrong!!!!!!

Windows("members_cleaned.xls").Activate
Range("AB2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-23]&RC[-22],'" & Bookname &
"'!newmiles,9,FALSE)"

However I word the formula, it comes up with Runtime error 1004 ...
Application- defined or object-defined error. Not being much cop at
this, I haven't a clue what it means..... except that when I practiced
the macro without a variable for the workbook name it was fine. Also,
I've tested it by printing the current variable into a cell an that's
correct....so what could it be?

Sorry the message is verbose - I just wanted to make it as clear as I
can. Can anyone help?????????

Phil
Click to expand...
 
S

ShaneDevenshire

Hi,

I don't have enough time to debug this code but here are a few suggestions:

1. Replace all of this:
Range("B65535").Select
ActiveCell.FormulaR1C1 = _

"=MID(CELL(""filename"",R[-65534]C),FIND(""["",CELL(""filename"",R[-65534]C)),FIND(""]"",CELL(""filename"",R[-65534]C))-
FIND(""["",CELL(""filename"",R[-65534]C)))& ""]"""

with:

BookName=ActiveWorkbook.Name

You can concatenate the [] is you want.

2. Replace this:

Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & iLastRow0),
Type:=xlFillDefault
Range("F2:F" & iLastRow0).Select

with

Columns("F:F").Insert
Range("F2") = "=RC[-2]&RC[-1]"
Range("F2:F" & iLastRow0).Select
Selection.FillDown

3. To get the formula right open both workbooks move to the cell where you
want the vlookup formula and type it in manually by pointing and clicking.
Once its working, select the cell and turn on the recorder. Press F2, Enter
and turn the recorder off.

--
Thanks,
Shane Devenshire


Phil said:
Please disregard the last bit - I hit the wrong button!!!!!

This is what I'm trying to say....

Hi Guys,

Not wishing to bother anyone here, I've been struggling with this all
weekend on my own, and got nowhere, so I'm turning to you all for
help!
Click to expand...

Here's the problem:1. I have a workbook open that needs to gather data from a series of
other workbooks - one at a time, and each decided by the user.......so
the macro needs to:

a) Let the user browse to the first workbook to be opened
b) Get the filename of that workbook
c) Concatenate two of the columns in that book
d) Define a range for a VLOOKUP in the already open book
e) Create said formula in the already open book

Currently I'm doing it using the following code, which posts the
worbook name in cell B65535 so I can use it as a variable (there's
probably a better way, but I can't think of one). All works fine until
I use that variable in the VLOOKUP formula and it all goes haywire.
I'm not sure if it's a syntax thing - although I've tried all sorsts
of ways - or something else. Here's the code.....

Sub Add_New_Miles()

Dim iLastRow0 As Long
Dim iLastRow1 As Long
Dim iLastRow2 As Long
Dim Bookname As String



FName = Application.GetOpenFilename()
If FName <> False Then
Workbooks.Open Filename:=FName
End If

Range("B65535").Select
ActiveCell.FormulaR1C1 = _

"=MID(CELL(""filename"",R[-65534]C),FIND(""["",CELL(""filename"",R[-65534]C)),FIND(""]"",CELL(""filename"",R[-65534]C))-
FIND(""["",CELL(""filename"",R[-65534]C)))& ""]"""

' This should give a result in the form [filename] to assign to the
variable below....

Bookname = Range("B65535").Value


' The next bit calculates the number of rows in the dataset....

iLastRow0 = Cells(Rows.Count, "A").End(xlUp).Row

' Now it concatenates two columns to use in the lookup later.....

Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F" & iLastRow0),
Type:=xlFillDefault
Range("F2:F" & iLastRow0).Select


' Now a range name ("newmiles") for the lookup is defined...

Range("F2:N" & iLastRow0).Name = "newmiles"

' Next, switch to other book and plant the VLOOKUP formula, which is
meant to look at two values in this spreadsheet and compare them with
the concatenated ones in the other (which works fine when doing this
manually).......This is the bit that's going wrong!!!!!!

Windows("members_cleaned.xls").Activate
Range("AB2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-23]&RC[-22],'" & Bookname &
"'!newmiles,9,FALSE)"


However I word the formula, it comes up with Runtime error 1004 ...
Application- defined or object-defined error. Not being much cop at
this, I haven't a clue what it means..... except that when I practiced
the macro without a variable for the workbook name it was fine. Also,
I've tested it by printing the current variable into a cell an that's
correct....so what could it be?

Sorry the message is verbose - I just wanted to make it as clear as I
can. Can anyone help?????????

Phil
Click to expand...
 
P

Phil

Thanks to both Dave and Shane!

I haven't had a chance to try it out yet, but both of you have offered
some very useful suggestions - I'll let you know how they all turned
out later.

Many thanks for taking the time to help me out with this

Phil
 
Top