CODEWITHSUNDEEP
vbaexcel
Masterarbeit3
Masterarbeit3

Reputation: 3

VBA Excel SumIf returns 0

I am using the SumIf-function in the code below to calculate the sum in column 309 (= what to sum) for all strings ValSrc (= what to find) in Column 308 (=where to find). The code causes no runtime error but although there should be results Excel just returns 0.

On Error GoTo Errorhandler
ValSrc = Cells(a, 48).Value & "/DIR/" & Cells(4, a).Value
ValFnd = Application.WorksheetFunction.SumIf(Range(Cells(5, 309), Cells(105673, 309)), Range(Cells(5, 308), Cells(105673, 308)), ValSrc)
Cells(a, b) = ValFnd

Edit: 

Sub Gewichte_SumIf()

Dim a As Integer, b As Integer
Dim ValSrc As Variant, ValFnd As Double

For a = 5 To 5
    For b = 49 To 300
        If Cells(a, 306) > 0 Then
            If Cells(3, b) = Cells(a, 33) Then
                On Error GoTo Errorhandler
                'ValSrc = .Cells(a, 48).Value & "/DIR/" & .Cells(4, a).Value
                With Worksheets("DS_(2)_Abfragen")
                    Val Fnd = Application.WorksheetFunction.SumIf(.Range(.Cells(5, 309), .Cells(105673, 309)), .Range(.Cells(5, 308), .Cells(105673, 308)), "53.541331,10.033631<53.4977094,10.1118412<52.9314509,9.2331748<monday/DIR/39454")
                    .Cells(a, b) = ValFnd
                End With
            Else
                Cells(a, b) = ""
            End If
        Else
        End If
    Next b
Next a

Exit Sub

Errorhandler:
Cells(a, b) = 0
Resume Next

End Sub

Upvotes: 0

Views: 417

Answers (2)

Vityata
Vityata

Reputation: 43585

Refer to the correct worksheet, using .Cells and .Range. Something like this:

With Worksheets("Name-Of-Worksheet")
    ValSrc = .Cells(a, 48).Value & "/DIR/" & .Cells(4, a).Value
    ValFnd = Application.WorksheetFunction.SumIf(.Range(.Cells(5, 309), .Cells(105673, 309)), .Range(.Cells(5, 308), .Cells(105673, 308)), ValSrc)
    .Cells(a, b) = ValFnd
End With

If you do not do it, it takes the ActiveSheet and probably this is not what you need.

This is the documentation of the function.

"The code causes no runtime error but although there should be results Excel just returns 0." - It returns 0, because of the On Error GoTo Errorhandler. Remove this line and see the run-time error.

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

The parameters for SumIf are:

  1. Search range
  2. Criteria
  3. Sum range

so you should use

ValFnd = Application.WorksheetFunction.SumIf(Range(Cells(5, 308), Cells(105673, 308)), _
                                             ValSrc, _
                                             Range(Cells(5, 309), Cells(105673, 309)))

Upvotes: 1

Related Questions