VBA Absolute Cell Reference

[Benzoli7]

How can I make this vlookup use absolute cell references?

"=VLOOKUP(RC[-1],LOOKUP!R[1]C[-18]:R[113]C[-17],2)"

Thanks for your help.

 

[Datsmart]

Paste the formula into a cell on your worksheet.
Select the cell.
Hit F2 to get into Edit mode.
Hit F4 to change to Absolute Reference.

 

[Benzoli7]

Hmm. Didn't work. Is there a line of code that I can add to that to make the reference absolute? I would like to change it to this format: A1:B2 but, it didn't work when I did that. Any suggestions? Thanks for your reply.

 

[Marcelo Branco]

Maybe this can help

http://msdn.microsoft.com/en-us/library/bb223281(v=office.12).aspx

M.

 

[Datsmart]

Sounds like you are talking about R1C1 reference style rather than Absolute Reference.

To change out of R1C1 style:
Click on the "Office Button"
Choose "Excel Options" at the bottom of the menu.
Click "Formulas"
In the "Working with formulas" section, uncheck "R1C1 reference style".

 

[Benzoli7]

Just checked for R1C1 formatting and the box was already unchecked. I do want to change out of this style of formatting if possible. If not, no big deal. I do however, need to make these references absolute because the current formula shifts the vlookup table down one row for everyrow that it is autofilled into. Example: Cell A1 can vlookup into range C1:D10 then cell A2 can vlookup into range C2:D11. I need A2 to have the ability to vlookup into A1. I hope that makes sense. If not, let me know and I will try to explain better. Thanks again for your help.

Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],LOOKUP!R[1]C[-18]:R[113]C[-17],2)"
'Dim LR As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("W2").AutoFill Destination:=Range("W2:W" & LR), Type:=xlFillDefault
Calculate

 

[Datsmart]

The Formula you listed in your code above would be as follows in "A1" Style:

=VLOOKUP(V2,LOOKUP!E1:F115,2)

If you want the Lookup Table to be Absolute beginning at Row 1, then it would read:

=VLOOKUP(V2,LOOKUP!$E$1:$F$115,2)

That would translate to this in R1C1 Style:

=VLOOKUP(RC[-1],LOOKUP!R1C5:R115C6,2)

Hope this is what you are after.