VLOOKUP Object error

[Simon]

I use the following below (The ranges set are for date stamping)

I wish values to go into MyWBAccRep to appear from MyWBSEL. However I
don't know how to refer the source workbook in the VLOOKUP function
correctly. The sheet it needs to look in is RSA and its within the
workbook MyWBSEL.

Dim MyWBAccRep As Workbook
Dim MyWBSEL As Workbook
Dim MyWBConvertor As Workbook


Set MyWBConvertor = Workbooks.Open("N:\mis\ Reporting\Latest
\AudaSourceConvertorv2.xls")

MyWBConvertor.Activate
Dim MyPath As String, MyRange As Range
Dim MyPath2 As String, MyRange2 As Range
MyPath = MyWBConvertor.Path
MyPath2 = MyWBConvertor.Path
Set MyRange = MyWBConvertor.ActiveSheet.Range("C25") ' Last month
Set MyRange2 = MyWBConvertor.ActiveSheet.Range("C26") ' Year

Set MyWBAccRep = Workbooks.Open("N:\mis\Reporting\" & MyRange.Value &
MyRange2.Value & "\" & "AccountsReport" & " " & MyRange.Value &
MyRange2.Value & ".xls")
Set MyWBSEL = Workbooks.Open("N:\mis\Reporting\" & MyRange.Value &
MyRange2.Value & "\" & "SEL Figures New Version" & " " & MyRange.Value
& MyRange2.Value & ".xls")

MyWBAccRep.Activate
Range("E9").FormulaR1C1 = _
"=VLOOKUP(R[-3]C, MyWBSEL!RSA!R1:R65536, 7, FALSE)"


Its this last line where I encounte problems.

 

[Dave Peterson]

If you build that formula manually, you'll find that you have something like:

=[book1.xls]Sheet1!$A$1:$E$9

In your formula, you're actually putting the characters "myWBELSEL" into the
formula--not the []'s and not the name of the file (with or without the path).

Range("E9").FormulaR1C1 = _
"=VLOOKUP(R[-3]C, MyWBSEL!RSA!R1:R65536, 7, FALSE)"

You could parse your strings and build a formula that looked like that string,
but it's a lot easier to let excel do the work:

Dim RCCLookUpRng as range
....
'After myWBSEL is open
'I used columns A:H, since you were only returning column 7
'why use all the columns????
set RCCLookupRng = mywbsel.worksheets("RSA").range("A:H")

Then...

MyWBAccRep.worksheets("BeSpecificHere").range("E9").formulaR1c1 _
= "=vlookup(r[-3]c," _
& rcclookuprng.address(referencestyle:=xlR1C1, external:=true) _
& ",7,false)"

(Untested, watch for typos!)

ps. By activating the workbook and then just plopping the formula in E9 of the
activesheet, you might not be on the sheet that actually needs to get the
formula.

If there's only one sheet in the workbook, I'd use:
MyWBAccRep.worksheets(1).range("E9").formulaR1c1 _

I use the following below (The ranges set are for date stamping)

I wish values to go into MyWBAccRep to appear from MyWBSEL. However I
don't know how to refer the source workbook in the VLOOKUP function
correctly. The sheet it needs to look in is RSA and its within the
workbook MyWBSEL.

Dim MyWBAccRep As Workbook
Dim MyWBSEL As Workbook
Dim MyWBConvertor As Workbook

Set MyWBConvertor = Workbooks.Open("N:\mis\ Reporting\Latest
\AudaSourceConvertorv2.xls")

MyWBConvertor.Activate
Dim MyPath As String, MyRange As Range
Dim MyPath2 As String, MyRange2 As Range
MyPath = MyWBConvertor.Path
MyPath2 = MyWBConvertor.Path
Set MyRange = MyWBConvertor.ActiveSheet.Range("C25") ' Last month
Set MyRange2 = MyWBConvertor.ActiveSheet.Range("C26") ' Year

Set MyWBAccRep = Workbooks.Open("N:\mis\Reporting\" & MyRange.Value &
MyRange2.Value & "\" & "AccountsReport" & " " & MyRange.Value &
MyRange2.Value & ".xls")
Set MyWBSEL = Workbooks.Open("N:\mis\Reporting\" & MyRange.Value &
MyRange2.Value & "\" & "SEL Figures New Version" & " " & MyRange.Value
& MyRange2.Value & ".xls")

MyWBAccRep.Activate
Range("E9").FormulaR1C1 = _
"=VLOOKUP(R[-3]C, MyWBSEL!RSA!R1:R65536, 7, FALSE)"

Its this last line where I encounte problems.