vba code: vlookup with absolute range

[houde]

Hello!
What do I need to substitute B2:G20000 with so the vba-code is not producing a debugging problem? Important: the range needs to be absolute, i.e. always B2:G20000 and not change when I copy it down to E2885. This is violated when I use the R1C1 notation, which is why I tried with B2G20000. But it's not working...

Worksheets("Matching").Activate
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-4],'FSA Figures'!B2:G20000,6,FALSE)"
Selection.AutoFill Destination:=Sheets("Matching").Range("E2:E2855"), Type:=xlFillDefault

I would be very happy for any help!
Thank you!
Fabian

 

[xlbo]

Same way as in a worksheet - use absolute referencing:

You are entering the formula as you want it to appear in the worksheet so:

"=VLOOKUP(RC[-4],'FSA Figures'!$B$2:$G$20000,6,FALSE)"

is what you are after - don't bother with the autofill tho - the following will do exactly the same:

Worksheets("Matching").Range("E2:E2855").Formula = _
"=VLOOKUP(A2,'FSA Figures'!$B$2:$G$20000,6,FALSE)"


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[houde]

Hey Geoff,
You're awesome!! Resolved all my problems in 2 minutes!
Cheers mate! ;-)
Fabian

 

[xlbo]

Just need to remember that you can use the .Formula as well as .FormulaR1C1

I prefer .Formula as I can write it how I would in a worksheet. Excel also has enough smarts in VBA to apply the absolute referencing when actually inputting the formulae so the A2 will increment down (as it has no $) but the lookup range does not (as it is fully absolute)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[houde]

Thank you!
I have one more question: I would like to replace 20000 by a placholder, e.g. alpha. Alpha is determined by a counter in the worksheet and tells me which one is the last row that contains data. Obviousely it doesn't work like this:

Sub UC_opp_model()
'
' UC_opp_model Macro
' Macro recorded 01/05/2005 by Fabian WYSS
'
'Define alpha that states the end of the vlookup range
Dim alpha As Integer
alpha = Sheets("FSA Figures").Range("K3").Value
'vlookup for pieces
Worksheets("Matching").Range("E2:E2855").Formula = _
"=VLOOKUP(A2,'FSA Figures'!$B$2:$G$alpha,6,FALSE)"
'vlookup for prices
Worksheets("Matching").Range("G2:G2855").Formula = _
"=VLOOKUP(A2,'FSA Figures'!$B$2:$G$alpha,5,FALSE)"

End Sub

What am I doing wrong?
Rgds, Fabian

 

[xlbo]

The formula is just a string so you concatenate like any other string:

Worksheets("Matching").Range("E2:E2855").Formula = _
"=VLOOKUP(A2,'FSA Figures'!$B$2:$G$" & Alpha & ",6,FALSE)"


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[houde]

Thank you very much! Actually I tried this before, but I got a "Expected: End of statement"-error. Then I copy-pasted it from your answer and it worked...
Hey, have a nice evening!!
Rgds, Fabian