Syntax for If Then SUMIFS-CodePudding

In the code below, I have identified a range (ColRng) in which I want to check each cell - if it is empty, there is a SUMIFS function to perform. It does not work. I know my syntax and logic is horrid, but I can't figure out how to make it work.

Dim ColRng As Range
Dim LastCol As Long
Dim LastRowScenario As Long
Dim x As Long
Dim rngCrit1 As Range
Dim rngCrit2 As Range
Dim rngSum As Range

LastRowScenario = Sheets("Sheet1").Range("Q2").End(xlDown).Row

Set rngCrit1 = Sheets("Sheet1").Range("D2:D" & LastRowScenario)
Set rngCrit2 = Sheets("Sheet1").Range("B2:B" & LastRowScenario)
Set rngSum = Sheets("Sheet1").Range("Q2:Q" & LastRowScenario)

LastCol = Sheets("Summary").Range("B5").End(xlToRight).Column
Set ColRng = Range(LastCol & "6:" & LastCol & "149")

For x = ColRng.Cells.Count To 1 Step -1
    With ColRng.Cells(x)
        ' If the cell is empty, perform a SUMIFS
         If IsEmpty(.Value) Then
  .Formula = Application.WorksheetFunction.SumIfs(rngSum, rngCrit1, .Range("E" & .Row).Value, rngCrit2, .Range("B" & .Row).Value)
        End If
    End With
Next x

CodePudding user response：

Your ColRng construction is wrong - try something like this instead:

Dim ColRng As Range
Dim LastCol As Long
Dim LastRowScenario As Long
Dim x As Long
Dim rngCrit1 As Range
Dim rngCrit2 As Range
Dim rngSum As Range, ws As Worksheet, wsSumm As Worksheet

set ws = Sheets("Sheet1")

Set rngSum = ws.Range("Q2:Q" & ws.Range("Q2").End(xlDown).Row)
Set rngCrit1 = rngSum.EntireRow.Columns("D")
Set rngCrit2 = rngSum.EntireRow.Columns("B")

Set wsSumm = Sheets("Summary")
With wsSumm.Range("B5").End(xlToRight).EntireColumn
    Set ColRng = wsSumm.Range(.Cells(6), .Cells(149))
End With

For x = ColRng.Cells.Count To 1 Step -1
    With ColRng.Cells(x)
        ' If the cell is empty, perform a SUMIFS
         If IsEmpty(.Value) Then
             .Formula = Application.SumIfs(rngSum, _
                  rngCrit1, .EntireRow.columns("E").Value, _
                  rngCrit2, .EntireRow.columns("B").Value)
        End If
    End With
Next x