VBA Using range variables for sumifs formula

[Boffa]

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"

 

[jasonb75]

You need to close the text string that makes up the formula and concatenate the vba ranges outside of the double quotes

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

hopefully I have them all in the right places.

 

[Boffa]

Bang on Jasonb75 ! Thank you, works a treat..

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