=sumif in VBA code

tpkelley_no

Board Regular
Joined
Oct 14, 2011
Messages
169
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.
Code:
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 & ")"
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Aaron Blood

Active Member
Joined
Oct 10, 2002
Messages
485
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:
Code:
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:
Code:
    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).
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top