VBA, FIlldown formula with changing cell reference

calbee

New Member
Joined
Nov 17, 2016
Messages
7

<tbody>
[TD="class: votecell"][/TD]
[TD="class: postcell"]

[/TD]

</tbody>


<tbody>
[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>
 

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
Back
Top