PDA

View Full Version : #NAME? Error with VBA



kelseyvdh
10-21-2018, 03:07 AM
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.

23072

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
10-21-2018, 07:22 AM
Solved by adding .FormulaLocal


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

Jan Karel Pieterse
10-22-2018, 01:31 AM
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
10-23-2018, 04:55 AM
Lijkt me voldoende:


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