#NAME? Error with VBA

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.

Attachment 23072

This is my code:

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!

Solved by adding .FormulaLocal

Code:

---

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

---

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:

Code:

---

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

---

Lijkt me voldoende:

Code:

---

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

---