Make formula to macro

[Alvaroro84]

Hello,
I tried to make a macro out of this formula while it works on one cell i cant get it to work with the rest of the column

=SUM(SUMIFS(C5:O5,$C$4:$O$4,{"Last","chev","tev","Pys"}))

This formula above works exactly like its suppose to. When i drop it down to the rest of the column it keep this "$C$4:$O$4" constant and changes only this "C5:O5" which is exactly how i want it to work and i tried to build a macro using that same logic. Issue with the bottom macro is that it only stays as =SUM(SUMIFS(C5:O5,$C$4:$O$4,{"Last","chev","tev","Pys"})) and through out the whole column it doesn't change this part "C5:O5"

Sub SumIfsMultipleOR()
    Dim sumRange As Range
    Dim criteriaRange As Range
    Dim result As Double
    Dim i As Integer
  
    Set sumRange = Range("C5:O5")
    Set criteriaRange = Range("$C$4:$O$4")
  
    Dim criteria As Variant
    criteria = Array("Last", "chev", "tev", "Pys")
    For i = 0 To UBound(criteria)
        result = WorksheetFunction.Sum(result, _
                    WorksheetFunction.SumIfs(sumRange, criteriaRange, criteria(i)))
    Next i
  
    Range("V:V") = result
End Sub

 

[Craggs82]

I can't test at the moment, but all you should need to do is iterate the sumRange inside the loop. give this a go.

Sub SumIfsMultipleOR()
    Dim sumRange As Range
    Dim criteriaRange As Range
    Dim result As Double
    Dim i As Integer

    Set criteriaRange = Range("$C$4:$O$4")
  
    Dim criteria As Variant
    criteria = Array("Last", "chev", "tev", "Pys")
    For i = 0 To UBound(criteria)
        Set sumRange = Range("C"&i+5&":O"&i+5")
        result = WorksheetFunction.Sum(result, _
                    WorksheetFunction.SumIfs(sumRange, criteriaRange, criteria(i)))
    Next i
  
    Range("V:V") = result
End Sub

 

[Alvaroro84]

I can't test at the moment, but all you should need to do is iterate the sumRange inside the loop. give this a go.

Sub SumIfsMultipleOR()
    Dim sumRange As Range
    Dim criteriaRange As Range
    Dim result As Double
    Dim i As Integer

    Set criteriaRange = Range("$C$4:$O$4")
 
    Dim criteria As Variant
    criteria = Array("Last", "chev", "tev", "Pys")
    For i = 0 To UBound(criteria)
        Set sumRange = Range("C"&i+5&":O"&i+5")
        result = WorksheetFunction.Sum(result, _
                    WorksheetFunction.SumIfs(sumRange, criteriaRange, criteria(i)))
    Next i
 
    Range("V:V") = result
End Sub

Im getting an issue where i marked the lettering yellow Set sumRange = Range("C"&i+5&":O"&i+5") the message says "expected: list separator or )"

 

[Craggs82]

seems i added an extra speech mark.

Sub SumIfsMultipleOR()
    Dim sumRange As Range
    Dim criteriaRange As Range
    Dim result As Double
    Dim i As Integer

    Set criteriaRange = Range("$C$4:$O$4")
  
    Dim criteria As Variant
    criteria = Array("Last", "chev", "tev", "Pys")
    For i = 0 To UBound(criteria)
        Set sumRange = Range("C"&i+5&":O"&i+5)
        result = WorksheetFunction.Sum(result, _
                    WorksheetFunction.SumIfs(sumRange, criteriaRange, criteria(i)))
    Next i
  
    Range("V:V") = result
End Sub

 

[Alvaroro84]

seems i added an extra speech mark.

Sub SumIfsMultipleOR()
    Dim sumRange As Range
    Dim criteriaRange As Range
    Dim result As Double
    Dim i As Integer

    Set criteriaRange = Range("$C$4:$O$4")
 
    Dim criteria As Variant
    criteria = Array("Last", "chev", "tev", "Pys")
    For i = 0 To UBound(criteria)
        Set sumRange = Range("C"&i+5&":O"&i+5)
        result = WorksheetFunction.Sum(result, _
                    WorksheetFunction.SumIfs(sumRange, criteriaRange, criteria(i)))
    Next i
 
    Range("V:V") = result
End Sub

Its odd I keep getting the message on the same spot

 

[Craggs82]

Same i just pasted it back in, apparently the gui doesn't like no spaces.

Set sumRange = Range("C" & i + 5 & ":O" & i + 5)

appears to be fine though

 

[Alvaroro84]

Same i just pasted it back in, apparently the gui doesn't like no spaces.

Set sumRange = Range("C" & i + 5 & ":O" & i + 5)

appears to be fine though

It works unfortunately it still only populates one number on all the cells

 

[Craggs82]

weird i focused on the type and not the rest of the code. it will, the result gets populated in the loop and overwrites itself every time so only the final iteration of the loop will count.

Are you expecting 4 results in the v column? you'll have to move the write into the loop.
This would write starting in cell V1

Sub SumIfsMultipleOR()
    Dim sumRange As Range
    Dim criteriaRange As Range
    Dim result As Double
    Dim i As Integer

    Set criteriaRange = Range("$C$4:$O$4")
  
    Dim criteria As Variant
    criteria = Array("Last", "chev", "tev", "Pys")
    For i = 0 To UBound(criteria)
        Set sumRange = Range("C" & i + 5 & ":O" & i + 5)
        result = WorksheetFunction.Sum(result, _
                    WorksheetFunction.SumIfs(sumRange, criteriaRange, criteria(i)))
        Range("V" & i + 1) = result
    Next i
  
End Sub

 

[Alvaroro84]

weird i focused on the type and not the rest of the code. it will, the result gets populated in the loop and overwrites itself every time so only the final iteration of the loop will count.

Are you expecting 4 results in the v column? you'll have to move the write into the loop.
This would write starting in cell V1

Sub SumIfsMultipleOR()
    Dim sumRange As Range
    Dim criteriaRange As Range
    Dim result As Double
    Dim i As Integer

    Set criteriaRange = Range("$C$4:$O$4")
 
    Dim criteria As Variant
    criteria = Array("Last", "chev", "tev", "Pys")
    For i = 0 To UBound(criteria)
        Set sumRange = Range("C" & i + 5 & ":O" & i + 5)
        result = WorksheetFunction.Sum(result, _
                    WorksheetFunction.SumIfs(sumRange, criteriaRange, criteria(i)))
        Range("V" & i + 1) = result
    Next i
 
End Sub

This specific one has about 1330 result but usually varies is there a way to stop it on the condition that column A is empty

 

[Alvaroro84]

This specific one has about 1330 result but usually varies is there a way to stop it on the condition that column A is empty

Also I'm not sure why but the criteria is summing incorrectly