Excel VBa to filldown formula with changing cell reference

[calbee]

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

<tbody style="margin: 0px; padding: 0px; border: 0px;">
[TD="class: votecell"]

0down votefavorite
​

[/TD]
[TD="class: postcell"]I have a Sumifs function to be done in macro with 2 criterias. my excel file is quite big. So my code looks like this :
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;"> Sub SumIfs ()
ThisWorkbook . Sheets ( "Sheet1" ). Activate
Range ( "L2" ) = Application . WorksheetFunction . SUMifs ( Range ( "G:G" ), Range ( "A:A" ), "Pen" , Range ( "F:F" ), "John" )
End Sub </code>But i would like to change the "Pen" to its cell reference, which means "A2" and " John" remains constant for all the cells down in the F:F range. And the to fill the formula down for all the cells below. I used this code,
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;"> Application . WorksheetFunction . SUMifs ( Range ( "G:G" ), Range ( "A:A" ), A2 , Range ( "F:F" ), "John" ) </code>But it only shows the value for A2 down in the cells when I did the filldown function. Please Help me with this. Thanks in Advance.


[/TD]

</tbody>

</body>

<tbody style="margin: 0px; padding: 0px; border: 0px;">
[TD="class: votecell"]

0down votefavorite
​

[/TD]
[TD="class: postcell"]I have a Sumifs function to be done in macro with 2 criterias. my excel file is quite big. So my code looks like this :
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;"> Sub SumIfs ()
ThisWorkbook . Sheets ( "Sheet1" ). Activate
Range ( "L2" ) = Application . WorksheetFunction . SUMifs ( Range ( "G:G" ), Range ( "A:A" ), "Pen" , Range ( "F:F" ), "John" )
End Sub </code>But i would like to change the "Pen" to its cell reference, which means "A2" and " John" remains constant for all the cells down in the F:F range. And the to fill the formula down for all the cells below. I used this code,
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;"> Application . WorksheetFunction . SUMifs ( Range ( "G:G" ), Range ( "A:A" ), A2 , Range ( "F:F" ), "John" ) </code>But it only shows the value for A2 down in the cells when I did the filldown function. Please Help me with this. Thanks in Advance.


[/TD]

</tbody>

 

[VbaA2z]

try any of the 2 below.

Range("L2").Formula = Application.WorksheetFunction.SumIfs(Range("G:G"), Range("A:A"), Range("A2"), Range("F:F"), Range("F2"))

Range("L2:L100").Formula = "=SUMIFS(G:G,A:A,A2,F:F,F2)"

 

[calbee]

Hi @VbaA2z,
the formula is not wrking, i tried the second, one with replacing the Range("L2:L100").Formula = "=SUMIFS(G:G,A:A,A2,F:F,F2)" "F2", with "John" , but im getiing a expected end of statement error., i used this formula,


Sub Sumifsfunction()




With ThisWorkbook.Sheets("Sheet1")
Range("L2:L100").Formula = "=SUMifs(G:G,A:A,E2,F:F,"John")"
End With
End Sub

 

[VbaA2z]

try

Sub Sumifsfunction()
With ThisWorkbook.Sheets("Sheet1").Range("L2:L100")
    .Formula = "=SUMifs(G:G,A:A,E2,F:F,""John"")"
    .Value = .Value
End With


End Sub

 

[calbee]

Thanks alot @VbaA2z, Its working well,