SUMIF formula which includes Lastrow

2
votes

I'm trying to enter a SUMIF formula into my VBA but the range of cells may change depending on the 'Lastrow' on another tab. I'm able to get the Lastrow no problem but the problem is trying to enter into my SUMIF formula. 'Lastrow' value should replace cells Q156 & H156. Hope this makes sense. Any suggestions welcome.

See below:

Sub UpdateReconData()

    Dim Lastrow6 As Integer

    'gets last Row on Formatting Spreadsheet
    Sheets("Formatting").Select
    Lastrow6 = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row

    Sheets("Recon").Select

    Range("B2").Select
    ActiveCell.Value = _
        "=SUMIF(Formatting!$Q$2:$Q$156(Recon!$A2&Recon!B$1),Formatting!$H$2:$H$156)"

End Sub
3
excelvba
I don't believe you are going to get much more than a #REF! error unless you add in a comma between the criteria_range and the criteria_value. – user4039065

3 Answers

0
votes

You can concatenate the Lastrow6 variable into the formula string. You can do this by using the ampersand (&) symbol to concatenate strings with variables.

Range("B2").Value = _
      "=SUMIF(Formatting!$Q$2:$Q$" & Lastrow6 & _
      "(Recon!$A2&Recon!B$1),Formatting!$H$2:$H$" & Lastrow6 & ")"
0
votes

Try this in your formula:

"=SUMIF(Formatting!$Q$2:$Q$" & Lastrow6 & "(Recon!$A2&Recon!B$1),Formatting!$H$2:$H$" & Lastrow6 & ")"
0
votes

Since the lastRow6 is in a couple of places, a Range.Replace method be easier than a string concatenation.

Sub UpdateReconData()
    Dim lastRow6 As Long

    'gets last Row on Formatting Spreadsheet
    With Worksheets("Formatting")
        lastRow6 = .Cells(Rows.Count, "B").End(xlUp).Row
    End With

    With Worksheets("Recon")
        .Range("B2").Formula = _
            Replace("=SUMIF(Formatting!$Q$2:$Q$XYZ, $A2&B$1, Formatting!$H$2:$H$XYZ)", _
                    "XYZ", lastRow6)
    End With
End Sub

I'm not entirely sure why you are not using full column references in the SUMIF function but I'm sure that there are other rows with data that coulod confuse the result. Full column references do not slow down a SUMIF / SUMIFS the way it does to a SUMPRODUCT function. Ideally, the lastRow6 could be built into the SUMIF formula like the following.

    With Worksheets("Recon")
        'never have to do this again.
        .Range("B2").Formula = _
            "=SUMIF(Formatting!$Q$2:INDEX(Formatting!$Q:$Q, MATCH(1e99, Formatting!$H:$H)), " & _
                "$A2&B$1, " & _
                "Formatting!$H$2:INDEX(Formatting!$H:$H, MATCH(1e99, Formatting!$H:$H)))"
    End With

The MATCH(1e99, Formatting!$H:$H) locates the last row in Formatting!H:H that contains a number. Since you are summing this column, no rows below that matter.