Relative/Absolute Formula writing in to a cell.

[Hari Prasadh]

Hi,

In the below example z= 2.

If I put,

ActiveCell.Formula = "= " & Cells(z, "d").Address

Then in Cell H8, I get the formula as -- = $D$2 --

How do I modify my formula such that I get the value as -- = D2 --

Actually I dont want hard-coded/fixed referencing.

Thanks a lot,
Hari
India

 

[Hari Prasadh]

Hi,

I recorded a macro for writing formula with absolute/half-absolute
references and got the following

Range("D15").Select
ActiveCell.FormulaR1C1 = "=R[-3]C[-2]"
Range("G17").Select
ActiveCell.FormulaR1C1 = "=R14C2"
Range("C18").Select
ActiveCell.FormulaR1C1 = "=R12C[-1]"
Range("B20").Select
ActiveCell.FormulaR1C1 = "=R[-6]C1"
Range("D21").Select
ActiveCell.FormulaR1C1 = "=R[-14]C[-1]"
Range("D22").Select

I can understand now that for my purpose I will have to use R1C1 method but
my problem is that I need to transform -- Cells(z, "d").Address -- where z
is a variable of for loop... How do I do it?

Please guide me.


Thanks a lot,
Hari
India

 

[Tom Ogilvy]

Cells(z, "d").Address(0,0)

--
Regards,
Tom Ogilvy

Hi,

I recorded a macro for writing formula with absolute/half-absolute
references and got the following

Range("D15").Select
ActiveCell.FormulaR1C1 = "=R[-3]C[-2]"
Range("G17").Select
ActiveCell.FormulaR1C1 = "=R14C2"
Range("C18").Select
ActiveCell.FormulaR1C1 = "=R12C[-1]"
Range("B20").Select
ActiveCell.FormulaR1C1 = "=R[-6]C1"
Range("D21").Select
ActiveCell.FormulaR1C1 = "=R[-14]C[-1]"
Range("D22").Select

I can understand now that for my purpose I will have to use R1C1 method but
my problem is that I need to transform -- Cells(z, "d").Address -- where z
is a variable of for loop... How do I do it?

Please guide me.


Thanks a lot,
Hari
India

Hi,

In the below example z= 2.

If I put,

ActiveCell.Formula = "= " & Cells(z, "d").Address

Then in Cell H8, I get the formula as -- = $D$2 --

How do I modify my formula such that I get the value as -- = D2 --

Actually I dont want hard-coded/fixed referencing.

Thanks a lot,
Hari
India

 

[Hari Prasadh]

Hi Tom,

Thanks it's working as desired.

Regards,,
Hari
India