Relative/Absolute Formula writing in to a cell.

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

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

"Hari Prasadh" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Use:
ActiveCell.Formula = "= " & Cells(z, "d").Address(rowAbsolute = True, columnabsolute = True)


- Mangesh

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

--
Regards,
Tom Ogilvy


"Hari Prasadh" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> "Hari Prasadh" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>

Hi Tom,

Thanks it's working as desired.

Regards,,
Hari
India

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Cells(z, "d").Address(0,0)
>
> --
> Regards,
> Tom Ogilvy
>
>