Consulting

Results 1 to 4 of 4

Thread: #NAME? Error with VBA

  1. #1

    #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.

    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!

  2. #2
    Solved by adding .FormulaLocal

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

  3. #3
    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 & ")"
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,718
    Lijkt me voldoende:

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •