#NAME? Error with VBA

[kelseyvdh]

Hi everyone,

I'm trying to create a SUM/formula within a new Excel workbook via VBA. The formula is correct. I get the correct range, but it doesn't sums up. Instead it gives a #NAME? error. When I select the range with the SUM and press Enter, it will correctly sums up the formula.

errorName.jpg

This is my code:

Private Sub hardwareTotaal()With Sheets("BLAD1")
        Dim sLookForOPMERKHARDWARE As String
        Dim sLookForHardware As String
        Dim oFoundOPMERKHARDWARE As Range
        Dim oFoundHardware As Range
        Dim oLookin As Range
        Dim eerstRegel As Range
        Dim laatsteRegel As String
        Dim totaal As String
        Dim totaalHW As Range
        
        sLookForHardware = "typenr. HW"
        sLookForOPMERKHARDWARE = "OPMERKINGEN HW:"
        
        Set oLookin = Worksheets("BLAD1").UsedRange
        
        Set oFoundHardware = oLookin.Find(What:=sLookForHardware, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)
        Set oFoundOPMERKHARDWARE = oLookin.Find(What:=sLookForOPMERKHARDWARE, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=True)
        
        If Not oFoundHardware Is Nothing Then
        eersteRegel = oFoundHardware.Row + 1
        laatsteRegel = oFoundOPMERKHARDWARE.Row - 3
        totaal = oFoundOPMERKHARDWARE.Row - 2
        Sheets("BLAD1").Range("H" & totaal) = "=SOM(" & "H" & eersteRegel & ":" & "H" & laatsteRegel & ")"
        
        End If
        
        Application.CalculateFullRebuild
    End With
End Sub

Any ideas? Many thanks in advance!

[kelseyvdh]

Solved by adding .FormulaLocal

        Sheets("BLAD1").Range("H" & totaal).FormulaLocal = "=SOM(" & "H" & eersteRegel & ":" & "H" & laatsteRegel & ")"

[Jan Karel Pieterse]

Never use FormulaLocal unless you absolutely have to. Instead, use Formula and use the American formula syntax so that your code always works, regardless of your users language settings:

Sheets("BLAD1").Range("H" & totaal).Formula = "=SUM(" & "H" & eersteRegel & ":" & "H" & laatsteRegel & ")"

[snb]

Lijkt me voldoende:

Sub M_snb()  
   Sheets("BLAD1").columns(6).find("totaal",,,1).offset(,1)= "=SUM(" & columns(7).specialcells(2,1).address & ")"
End Sub