VBA Using range variables for sumifs formula

Boffa

New Member
Joined
May 8, 2019
Messages
23
HI Guys need some help with this one - Done some searching but couldn't find anything that helps with my particular challenge here

The excel function I would to return via vba in Cell L3 is =SUMIFS(G3:G12,H3:H12,"Sales",I3:I12,"<>Excluded")/38

I want to achieve this using VBA and use variables to pass the ranges through to as the range sizes change

Below is my attempt but it does not seem to work

Any help would be greatly appreciated...

Dim LRow As Long
Dim SumRng As Range
Dim Criteria1Rng As Range
Dim Criteria2Rng As Range

LRow = Cells(Rows.count, 1).End(xlUp).row

Set SumRng = Range("G2", "G" & LRow)
Set Criteria1Rng = Range("H2", "H" & LRow)
Set Criteria2Rng = Range("I2", "I" & LRow)

Range("L3").Formula = "=SUMIFS(SumRng.address,Criteria1Rng.address,""Sales"",Criteria2Rng.address,""<>Excluded"")/38"
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,453
Office Version
  1. 365
Platform
  1. Windows
You need to close the text string that makes up the formula and concatenate the vba ranges outside of the double quotes
VBA Code:
Range("L3").Formula = "=SUMIFS(" & SumRng.address & "," & Criteria1Rng.address & ",""Sales""," & Criteria2Rng.address & ",""<>Excluded"")/38"
hopefully I have them all in the right places.
 

Boffa

New Member
Joined
May 8, 2019
Messages
23
Bang on Jasonb75 ! Thank you, works a treat..

Going crazy trying to get those double quotes in the right places
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,688
Members
416,127
Latest member
MALEPINZON

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