=sumif in VBA code

[tpkelley_no]

I am trying to do an =sumif VBA code. the formula needs to go in the cell that is three cells below the last text cell in column “C”. Here is the formula in excel. =SUMIF(B6:B237,"LAB",K6:K237). The variables will be in column “B” and “K” with the number of rows. I came up with this VBA code but it does not word. Thanks for the help.

Dim TLCP As Range, BLOOKUP As Range, KLOOKUP As Range
    Set TLCP = Range("C6").End(xlDown).Offset(8, 0)
    Set BLOOKUP = Range("B6").End(xlDown).Row.Count
    Set KLOOKUP = Range("K6").End(xlDown).Row.Count
   Cells(TLCP).Formula = "=SUMIF(" & Range("B6:B" & BLOOKUP).Address & ",""LAB""," _
            & Range("K6:K" & KLOOKUP).Address & ")"

 

[Aaron Blood]

I see you are trying to set a range variable as an integer row count value; so no that won't work...

The way I would set that up is:

Dim BLOOKUP As Range
Set BLOOKUP = Range("B6")
Set BLOOKUP = Range(BLOOKUP, BLOOKUP.End(xlDown))
 
Dim KLOOKUP As Range
Set KLOOKUP = Range("K6")
Set KLOOKUP = Range(KLOOKUP, KLOOKUP.End(xlDown))

Dim TLCP As Range
Set TLCP = Range("C6").End(xlDown).Offset(8, 0)
TLCP.Formula = "=SUMIF(" & BLOOKUP.Address & ",""LAB""," & KLOOKUP.Address & ")"

Now on a side note... there is an easy trick to moving cell-based formulas into VBA and letting VBA do the work of swapping out the references for you. If I know the correct structure of my formula for a cell, I can setup my code like this:

    Dim BLOOKUP As Range
    Set BLOOKUP = Range("B6")
    Set BLOOKUP = Range(BLOOKUP, BLOOKUP.End(xlDown))
    
    Dim KLOOKUP As Range
    Set KLOOKUP = Range("K6")
    Set KLOOKUP = Range(KLOOKUP, KLOOKUP.End(xlDown))

    Dim Fn As String
    Fn = "=SUMIF(B6:B237,""LAB"",K6:K237)"
    Fn = "=SUMIF(BLOOKUP,""LAB"",KLOOKUP)"
    Fn = Replace(Fn, "BLOOKUP", BLOOKUP.Address)
    Fn = Replace(Fn, "KLOOKUP", KLOOKUP.Address)

    Dim TLCP As Range
    Set TLCP = Range("C6").End(xlDown).Offset(8, 0)
    TLCP.Formula = Fn

Notice I start with the formula structure that I know works:
Fn = "=SUMIF(B6:B237,""LAB"",K6:K237)"

Then I just manually change the range references to my defined range variables in VBA:
Fn = "=SUMIF(BLOOKUP,""LAB"",KLOOKUP)"

...and then just use the replace command n times to swap out each range variable name with its corresponding address. This way if I need to go back and make a change to my starting cell formula, I can more easily edit my beginning Fn string in terms of how I am accustomed to editing formulas in cells rather than having to carefully step through a concatenated string conglomeration (as in the first example).

 

[tpkelley_no]

thanks it works, and I will keep the post for the info.!!