Getting SumIf values with VBA

I have a number of formulas in Column AZ like this:
=SUMIF(AllConfigs,AV6,AllJ)

I'd like to have the option of using VBA to produce the same results. When I run this code below, I get all zeroes.  Is there a way to make this work?

Thanks,
John


Sub SumIfs()
    Dim cel As Range
    For Each cel In Range("AZ6:AZ10")
    Dim str As String
    str = cel.Offset(0, -4).Address
    cel = Application.WorksheetFunction.SumIf([AllConfigs], str, [AllJ])
    Next
    End Sub
    End Sub

Open in new window

LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
cel.formula = "=sumif(Allconfigs," & str & ",AllJ)"
cel.value = cel.value
0
 
Michael FowlerSolutions ConsultantCommented:
John

If you are using VBA why not just write the logic yourself for the function

The code below get a total for range A1:A20 where the value in the same row for Column B is equal to
the value in C1
Sub sumifs()

   Dim i As Integer, j As Integer
   Dim sumif As Long
   Dim test As Long
     
   test = Range("C1").Value
     
   For i = 6 To 10
      sumif = 0
      For j = 1 To 20
         If Range("B" & j).Value = test Then
            sumif = sumif + Range("A" & j).Value
         End If
      Next
      Range("AZ" & i).Value = sumif
   Next

End Sub

Open in new window

0
 
TommySzalapskiConnect With a Mentor Commented:
That's because in VBA code you use the actual ranges not the addresses.
Try
cel = Application.WorksheetFunction.SumIf(Range("AllConfigs"), cel.Offset(0, -4).Text, Range("AllJ"))

I assume cel.Offset(0,-4) contains the condition (don't put quotes around it)
0
 
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Great answers, thanks.

Michael, I tried yours as-is and with several attempts at modification and couldn't get it to work. If it does indeed work, pls forgive me for leaving you out.

- John
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.