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

putting formula in cel gives #NAME error
 

Use English function name for the Formula property

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

should be

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

--
Regards,
Tom Ogilvy

"Rinze Smit" wrote in message
l.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

putting formula in cel gives #NAME error
 

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" wrote in message
...
Use English function name for the Formula property

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

should be

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

--
Regards,
Tom Ogilvy

"Rinze Smit" wrote in message
l.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