Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup using a variable for the workbook name
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup using a variable for the workbook name
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! 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup using a variable for the workbook name
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 Phil wrote: 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! 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup using a variable for the workbook name
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" wrote: 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! 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup using a variable for the workbook name
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
set a variable based on a vlookup | Excel Discussion (Misc queries) | |||
Can not set WorkBook variable | Excel Discussion (Misc queries) | |||
vlookup with variable col_index_num | Excel Worksheet Functions | |||
Variable col_index_num in vlookup | Excel Discussion (Misc queries) | |||
How do I link to a workbook whose name is variable? | Excel Discussion (Misc queries) |