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!