putting formula in cel gives #NAME error

[Rinze Smit]

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

 

[Tom Ogilvy]

Use English function name for the Formula property

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

should be

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

 

[Rinze Smit]

Hi Tom,

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

Rinze