Vlookup using two defined workbooks

[confused in Frankfurt]

Hello,
I have two workbooks which I have defined oldfile and newfile
in the old file I have defined vrange to be used for vlookup
the worksheet name in both files is called "South Africa"

I want to create a vlookup formula using the defined name, range

Sub
workbooks (newfile).Activate
cells(3,13)activate

I tried two forms of Vlookup neither work
1)

On Error Resume Next

x = Application.WorksheetFunction.VLookup(Cell.Offset(0, -12).Value, _
Workbooks(oldfile).Worksheets("South Africa").Range(vrange), 13, 0)
If Err.Number <> 0 Then
Cell.Offset(1, 0).Select
Else
Cell.Value = x
End If

2)
On Error Resume Next

Active cell.formulaR1C1= _
"=vlookup(RC[-12], workbooks(oldfile).worksheets("South Africa").Range(vrange),13,0)
next cell

Appreciate your help

 

[Andrew Poulsom]

vrange should be in double quotes. You are using VBA syntax rather than Excel syntax in your formula. I suggest you record a macro while entering the formula manually to get the correct syntax, which you can then edit to include variables.

 

[confused in Frankfurt]

hi Andrew,
recorded the macro
"=VLOOKUP(RC[-12],'[ROOSScenario Q1_2011 02 11.xls]South Africa'!R3C1:'[ROOSScenario Q1_2011 02 11.xls]South Africa'!R27C16,13,0)"

now I am confused what to replace
xls file is Oldfile and the range I had defined as vrange earlier

regards
Sarah

 

[Andrew Poulsom]

That doesn't look right - the file name should only appear once. Record a macro while using the named range in your formula and post what you get.

 

[confused in Frankfurt]

the range is defined in the VBA and not as Named range in excel, how can i record using this if the macro to set the name is in the other macro

I set the range manually in excel and recorded this macro.

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-12],'ROOSScenario Q1_2011 02 11.xls'!vrange,13,0)"

how do I replace the xls with oldfile (defined name) and do I have to write something to the range as well?

I have a number of sheets in the workbooks which have to be updated from the old files do I have to define the ranges in each sheet with a new name, or will it loop through overwriting vrange each time it moves from one sheet to the next?

 

[Andrew Poulsom]

Try:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-12],'" & oldfile & "'!vrange,13,0)"

 

[confused in Frankfurt]

thanks, the first part is now working but it looks like it cant find the range as defined below:

Dim vrange As Range
Cells(3, 1).Select
Range(Selection, Cells(3, 16)).Select
Range(Selection, Selection.End(xlDown)).Select

Set vrange = Selection

 

[Andrew Poulsom]

I thought vrange was a named range. Try:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-12],'" & oldfile & "'!" & vrange.Address(, , xlR1C1) & ",13,0)"

 

[confused in Frankfurt]

Hi Andrew - all running correctly now.

Thanks once again for helping a "taught yourself via googling" vba user )

lots of trial and error we all reach our goal.

 

[confused in Frankfurt]

Hi Andrew

now I have switched to sheet BeLux in both old an newfile but the formula is connecting the vlookup to the south african sheet although I have defined the range as BErange in the new sheet and used this in the formula. I can't tell where the formula is telling which sheet, can you direct me the right way?