Insert variable cell in the formula

Hi,

I try to insert a formula to multiply two rows, like A1*C1, A2*C2....., how
can I do that by using a FOR loop in the marco?

Thanks.

Gian

You don't need to loop
Assume you want the formula in column D

set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
rng.offset(0,3).Formula = "=A1*C1"

--

Regards,
Tom Ogilvy

"Gian" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I try to insert a formula to multiply two rows, like A1*C1, A2*C2.....,
how
> can I do that by using a FOR loop in the marco?
>
> Thanks.
>
> Gian

Thanks Tom.

I just curious, it's possible to put the variable in the formula, like
"=chr(65)&1 * chr(67)&1"

"Tom Ogilvy" wrote:

> You don't need to loop
> Assume you want the formula in column D
>
> set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
> rng.offset(0,3).Formula = "=A1*C1"
>
> --
>
> Regards,
> Tom Ogilvy
>
> "Gian" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I try to insert a formula to multiply two rows, like A1*C1, A2*C2.....,
> how
> > can I do that by using a FOR loop in the marco?
> >
> > Thanks.
> >
> > Gian
>
>
>

"=" & chr(65) & "1" & "*" & chr(67) & "1"

testing from the immediate window:

? "=" & chr(65) & "1" & "*" & chr(67) & "1"
=A1*C1

But I am not sure that is what you are trying to achieve.

--
Regards,
Tom Ogilvy



"Gian" <[email protected]> wrote in message
news:[email protected]...
> Thanks Tom.
>
> I just curious, it's possible to put the variable in the formula, like
> "=chr(65)&1 * chr(67)&1"
>
> "Tom Ogilvy" wrote:
>
> > You don't need to loop
> > Assume you want the formula in column D
> >
> > set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
> > rng.offset(0,3).Formula = "=A1*C1"
> >
> > --
> >
> > Regards,
> > Tom Ogilvy
> >
> > "Gian" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > >
> > > I try to insert a formula to multiply two rows, like A1*C1,
A2*C2.....,
> > how
> > > can I do that by using a FOR loop in the marco?
> > >
> > > Thanks.
> > >
> > > Gian
> >
> >
> >

Hi Tom,

This is exactly what I want. Appreciate your help.

BTW, what the "End(xlup)" for?

Thanks


"Tom Ogilvy" wrote:

> "=" & chr(65) & "1" & "*" & chr(67) & "1"
>
> testing from the immediate window:
>
> ? "=" & chr(65) & "1" & "*" & chr(67) & "1"
> =A1*C1
>
> But I am not sure that is what you are trying to achieve.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Gian" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Tom.
> >
> > I just curious, it's possible to put the variable in the formula, like
> > "=chr(65)&1 * chr(67)&1"
> >
> > "Tom Ogilvy" wrote:
> >
> > > You don't need to loop
> > > Assume you want the formula in column D
> > >
> > > set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
> > > rng.offset(0,3).Formula = "=A1*C1"
> > >
> > > --
> > >
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Gian" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi,
> > > >
> > > > I try to insert a formula to multiply two rows, like A1*C1,
> A2*C2.....,
> > > how
> > > > can I do that by using a FOR loop in the marco?
> > > >
> > > > Thanks.
> > > >
> > > > Gian
> > >
> > >
> > >
>
>
>

go well below you data and hit the End Key, then the Up Arrow key. It takes
you to the last cell containing data in that column. It is the same in
code. So I am finding the extent of your data in column A.

--
Regards,
Tom Ogilvy

"Gian" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Tom,
>
> This is exactly what I want. Appreciate your help.
>
> BTW, what the "End(xlup)" for?
>
> Thanks
>
>
> "Tom Ogilvy" wrote:
>
> > "=" & chr(65) & "1" & "*" & chr(67) & "1"
> >
> > testing from the immediate window:
> >
> > ? "=" & chr(65) & "1" & "*" & chr(67) & "1"
> > =A1*C1
> >
> > But I am not sure that is what you are trying to achieve.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Gian" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks Tom.
> > >
> > > I just curious, it's possible to put the variable in the formula, like
> > > "=chr(65)&1 * chr(67)&1"
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > You don't need to loop
> > > > Assume you want the formula in column D
> > > >
> > > > set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
> > > > rng.offset(0,3).Formula = "=A1*C1"
> > > >
> > > > --
> > > >
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Gian" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi,
> > > > >
> > > > > I try to insert a formula to multiply two rows, like A1*C1,
> > A2*C2.....,
> > > > how
> > > > > can I do that by using a FOR loop in the marco?
> > > > >
> > > > > Thanks.
> > > > >
> > > > > Gian
> > > >
> > > >
> > > >
> >
> >
> >