Formula with relative references

M

Miguel Coego

Hi,

I´m trying to create a formula in VBA that relates sheets
with a fixed value.

Instead of:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],'[Conciliacion 012.xls]E012'!
R4C1:R369C4,3,0)

I need to use something like:

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-8],'[Conciliacion 012.xls]X'!
R4C1:R369C4,3,0)

where X is a changeable value.

Any idea?

Thanks very much indeed!!!!!!!!!!!!!!!
 
P

pug

Is X defined by the code?



So you could use

....."=3DVLOOKUP(RC[-8],'[Conciliacion 012.xls]" & SheetNameVariable
& "'!.....



or if it is defined by the worksheet, perhaps use the indirect function.
 
I

Iolaire McFadden

Something like this?
<code>
number_of_markets = 10
Bottom = 54 + number_of_markets - 1
bottom_code = "R" & Bottom & "C11"
ActiveCell.FormulaR1C1 = "=RANK(RC[10],R54C11:" &
bottom_code & ",0)"
</code>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine 2 columns in a dynamic range into one 7
Vlookup in vba - how to use absolute rows not relative 1
Reference the previous Worksheet in a Formula 3
Macro File path problem 0
If statement(rookies) 3
to create a formula by removing RC[number] 2
Adding values stored at relative cell references 1
Insert Formula in cells from VBA 2

Top