write a formula to a cell using VBA

[Souris]

I wnated to write a simple formula like "=SUM(C12:C18)" in to a cell using VBA.

I have following code

Sheets(wsDestination).Cells(I, eColumn).Formula = "=SUM(C12:C18)"

The problme is I have the code in a for loop and I need change the cells
depends on the for loop index like

when I = 1 then "=SUM(C12:F12)"
when I = 2 then "=SUM(C13:F13)"
when I = 3 then "=SUM(C14:F14)"

The C and F depend on a variable to control.

Any information is great appreciated,

[Souris]

I have following code, but I got run time error 1004.
Application Defined or Object defined error.


Sheets(wsDestination).Cells(I, eColumn).Formula = "=SUM(G" & I & ":" &
Chr(eColumn + 40) & I & ")"

"Souris" wrote:

> I wnated to write a simple formula like "=SUM(C12:C18)" in to a cell using VBA.
>
> I have following code
>
> Sheets(wsDestination).Cells(I, eColumn).Formula = "=SUM(C12:C18)"
>
> The problme is I have the code in a for loop and I need change the cells
> depends on the for loop index like
>
> when I = 1 then "=SUM(C12:F12)"
> when I = 2 then "=SUM(C13:F13)"
> when I = 3 then "=SUM(C14:F14)"
>
> The C and F depend on a variable to control.
>
> Any information is great appreciated,
>

[Norman Jones]

Hi Souris,

Try:

Sub TesterA()
Dim I As Long

For I = 1 To 3
Sheets(wsDestination).Cells(I, eColumn).Formula = _
"=SUM(C" & I + 11 & ":F" & I + 11 & ")"
Next I

End Sub


---
Regards,
Norman



"Souris" <Souris@discussions.microsoft.com> wrote in message
news:EFC64C82-EE52-4370-92FA-2A79E675919A@microsoft.com...
>I wnated to write a simple formula like "=SUM(C12:C18)" in to a cell using
>VBA.
>
> I have following code
>
> Sheets(wsDestination).Cells(I, eColumn).Formula = "=SUM(C12:C18)"
>
> The problme is I have the code in a for loop and I need change the cells
> depends on the for loop index like
>
> when I = 1 then "=SUM(C12:F12)"
> when I = 2 then "=SUM(C13:F13)"
> when I = 3 then "=SUM(C14:F14)"
>
> The C and F depend on a variable to control.
>
> Any information is great appreciated,
>

[Souris]

Thanks millions,

It works,




"Norman Jones" wrote:

> Hi Souris,
>
> Try:
>
> Sub TesterA()
> Dim I As Long
>
> For I = 1 To 3
> Sheets(wsDestination).Cells(I, eColumn).Formula = _
> "=SUM(C" & I + 11 & ":F" & I + 11 & ")"
> Next I
>
> End Sub
>
>
> ---
> Regards,
> Norman
>
>
>
> "Souris" <Souris@discussions.microsoft.com> wrote in message
> news:EFC64C82-EE52-4370-92FA-2A79E675919A@microsoft.com...
> >I wnated to write a simple formula like "=SUM(C12:C18)" in to a cell using
> >VBA.
> >
> > I have following code
> >
> > Sheets(wsDestination).Cells(I, eColumn).Formula = "=SUM(C12:C18)"
> >
> > The problme is I have the code in a for loop and I need change the cells
> > depends on the for loop index like
> >
> > when I = 1 then "=SUM(C12:F12)"
> > when I = 2 then "=SUM(C13:F13)"
> > when I = 3 then "=SUM(C14:F14)"
> >
> > The C and F depend on a variable to control.
> >
> > Any information is great appreciated,
> >
>
>
>

[Mike Fogleman]

Dim rw As Long

rw = 12 'the row number you want in formula

For I = 1 to 50
Sheets(wsDestination).Cells(I, eColumn).Formula = "=SUM(C& rw:F& rw)"
rw = rw + I
Loop

Mike F
"Souris" <Souris@discussions.microsoft.com> wrote in message
news:EFC64C82-EE52-4370-92FA-2A79E675919A@microsoft.com...
>I wnated to write a simple formula like "=SUM(C12:C18)" in to a cell using
>VBA.
>
> I have following code
>
> Sheets(wsDestination).Cells(I, eColumn).Formula = "=SUM(C12:C18)"
>
> The problme is I have the code in a for loop and I need change the cells
> depends on the for loop index like
>
> when I = 1 then "=SUM(C12:F12)"
> when I = 2 then "=SUM(C13:F13)"
> when I = 3 then "=SUM(C14:F14)"
>
> The C and F depend on a variable to control.
>
> Any information is great appreciated,
>