CODEWITHSUNDEEP
excelvba
Conor
Conor

Reputation: 125

SUMIF formula which includes Lastrow

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

Upvotes: 2

Views: 1229

Answers (3)

user4039065
user4039065

Reputation:

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.

Upvotes: 0

Canute
Canute

Reputation: 69

Try this in your formula:

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

Upvotes: 0

luke_t
luke_t

Reputation: 2975

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 & ")"

Upvotes: 0

Related Questions