putting formula in cel gives #NAME error

Hi,
I'm trying to put a formula into a cel, using the code below (which I also
found on a newsgroup).
The formula gets into the cel the right way, but the value displayed is an
errormessage "#NAME".
However. When I manually select the cell, press F2 and Enter. The value is
displayed correctly.

Can anyone tell me how I put this into my code?

Many Thanks,

Rinze Smit,
Revalidatie Friesland

Sub Formuleplaatsen()

' Go to first cell of column

Range("D2").Select



' Test for entry in row2, if blank, use End Down to go to first non-blank
cell

If ActiveCell.Value = "" Then Selection.End(xlDown).Select



' Capture address of first cell of range

StartA = ActiveCell.Address(rowabsolute:=False, columnabsolute:=False)



' Go to bottom of range

Selection.End(xlDown).Select



' Capture address of end of range

EndA = ActiveCell.Address(rowabsolute:=False, columnabsolute:=False)



' Move to cell to enter formula

ActiveCell.Offset(2, 0).Select



' Define formula

Totaal = "=Som(" & StartA & " : " & EndA & ")"



' Enter formula into worksheet

ActiveCell.Formula = Totaal



End Sub

Use English function name for the Formula property

Totaal = "=Som(" & StartA & " : " & EndA & ")"

should be

Totaal = "=SUM(" & StartA & " : " & EndA & ")"

--
Regards,
Tom Ogilvy

"Rinze Smit" <rinze.smit@revalidatie-friesland.nl> wrote in message
news:65c07$42c2b4c0$c3238ce3$17430@news.essentkabel.com...
> Hi,
> I'm trying to put a formula into a cel, using the code below (which I also
> found on a newsgroup).
> The formula gets into the cel the right way, but the value displayed is an
> errormessage "#NAME".
> However. When I manually select the cell, press F2 and Enter. The value is
> displayed correctly.
>
> Can anyone tell me how I put this into my code?
>
> Many Thanks,
>
> Rinze Smit,
> Revalidatie Friesland
>
> Sub Formuleplaatsen()
>
> ' Go to first cell of column
>
> Range("D2").Select
>
>
>
> ' Test for entry in row2, if blank, use End Down to go to first
non-blank
> cell
>
> If ActiveCell.Value = "" Then Selection.End(xlDown).Select
>
>
>
> ' Capture address of first cell of range
>
> StartA = ActiveCell.Address(rowabsolute:=False, columnabsolute:=False)
>
>
>
> ' Go to bottom of range
>
> Selection.End(xlDown).Select
>
>
>
> ' Capture address of end of range
>
> EndA = ActiveCell.Address(rowabsolute:=False, columnabsolute:=False)
>
>
>
> ' Move to cell to enter formula
>
> ActiveCell.Offset(2, 0).Select
>
>
>
> ' Define formula
>
> Totaal = "=Som(" & StartA & " : " & EndA & ")"
>
>
>
> ' Enter formula into worksheet
>
> ActiveCell.Formula = Totaal
>
>
>
> End Sub
>
>

Hi Tom,

I thought I'd already tried that..... But NO. My memory had 'left' me.
Worked out fine. Thanks a lot.

Rinze



"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uJSfZYMfFHA.3612@TK2MSFTNGP12.phx.gbl...
> Use English function name for the Formula property
>
> Totaal = "=Som(" & StartA & " : " & EndA & ")"
>
> should be
>
> Totaal = "=SUM(" & StartA & " : " & EndA & ")"
>
> --
> Regards,
> Tom Ogilvy
>
> "Rinze Smit" <rinze.smit@revalidatie-friesland.nl> wrote in message
> news:65c07$42c2b4c0$c3238ce3$17430@news.essentkabel.com...
> > Hi,
> > I'm trying to put a formula into a cel, using the code below (which I
also
> > found on a newsgroup).
> > The formula gets into the cel the right way, but the value displayed is
an
> > errormessage "#NAME".
> > However. When I manually select the cell, press F2 and Enter. The value
is
> > displayed correctly.
> >
> > Can anyone tell me how I put this into my code?
> >
> > Many Thanks,
> >
> > Rinze Smit,
> > Revalidatie Friesland
> >
> > Sub Formuleplaatsen()
> >
> > ' Go to first cell of column
> >
> > Range("D2").Select
> >
> >
> >
> > ' Test for entry in row2, if blank, use End Down to go to first
> non-blank
> > cell
> >
> > If ActiveCell.Value = "" Then Selection.End(xlDown).Select
> >
> >
> >
> > ' Capture address of first cell of range
> >
> > StartA = ActiveCell.Address(rowabsolute:=False,
columnabsolute:=False)
> >
> >
> >
> > ' Go to bottom of range
> >
> > Selection.End(xlDown).Select
> >
> >
> >
> > ' Capture address of end of range
> >
> > EndA = ActiveCell.Address(rowabsolute:=False, columnabsolute:=False)
> >
> >
> >
> > ' Move to cell to enter formula
> >
> > ActiveCell.Offset(2, 0).Select
> >
> >
> >
> > ' Define formula
> >
> > Totaal = "=Som(" & StartA & " : " & EndA & ")"
> >
> >
> >
> > ' Enter formula into worksheet
> >
> > ActiveCell.Formula = Totaal
> >
> >
> >
> > End Sub
> >
> >
>
>