Macro to Modify "SUMIF" Formula Range

norts55

Board Regular
Joined
Jul 27, 2012
Messages
144
Hello,
I need a macro to somehow modify the row range (rows only, not the columns) of the formula =SUMIF('Takeoff Sheet'!E1:E30,"Incidentals",'Takeoff Sheet'!F1:F30) to match the range of cells I currently have selected. I have this formula stored in a named cell "_0_a_a_Incidentals" so it will be in the same location at all times. After the range is changed I can then copy and paste this formula to where I need it. I should be able to figure out the copy and paste portion of the macro but I have no idea where to start to modify the range.

Below are the steps...
Step One: I select a group of cells from my sheet called "Takeoff Sheet". example: E8:O120. Note: this will be a random group every time.
Step Two: Run this macro
Step Three: This macro would find the named cell "_0_a_a_Incidentals" which contains the formula =SUMIF('Takeoff Sheet'!E1:E30,"Incidentals",'Takeoff Sheet'!F1:F30)
Step Four: This macro would modify the contents of this formula to =SUMIF('Takeoff Sheet'!E8:E120,"Incidentals",'Takeoff Sheet'!F8:F120)

Any help or ideas would be greatly appreciated. Thanks to anyone to takes on this challenge.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,046
Office Version
  1. 365
Platform
  1. Windows
Here's one way:

Code:
Sub Test()

    Dim rng As Range
    Dim lFirst As Long, lLast As Long
    
    On Error Resume Next
    Set rng = Application.InputBox("Please specify range", , , , , , , 8)
    On Error GoTo 0
    If Not rng Is Nothing Then
        lFirst = rng.Row
        lLast = rng.Rows.Count + lFirst - 1
        Range("_0_a_a_Incidentals").Formula = "=SUMIF('Takeoff Sheet'!E" & lFirst & ":E" & lLast _
            & ",""Incidentals"",'Takeoff Sheet'!F" & lFirst & ":F" & lLast & ")"
    End If

End Sub
 

norts55

Board Regular
Joined
Jul 27, 2012
Messages
144
Stephen, this works great but is there a way to make a couple of modifications???

Is there a way to have the formula be absolute - [=SUMIF(Takeoff!$E$8:$E$120,"Incidentals",Takeoff!$F$8:$F$120)]? I added a copy and paste routine after your macro runs and I am finding unless the formula has absolute cells I am getting REF errors - [=SUMIF(Takeoff!#REF!,"Incidentals Cost",Takeoff!#REF!)] - after the paste happens.

Is there a way to have the dialog box read my current selection? I will already have the row range selected because I have some other formatting happening prior to this running. If not, no big deal, I can live with it the way it is.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,046
Office Version
  1. 365
Platform
  1. Windows
To make the row references absolute:

Code:
Range("_0_a_a_Incidentals").Formula = "=SUMIF('Takeoff Sheet'!E[COLOR=#ff0000][B]$[/B][/COLOR]" & lFirst & ":E[COLOR=#ff0000][B]$[/B][/COLOR]" & lLast _
    & ",""Incidentals"",'Takeoff Sheet'!F[COLOR=#ff0000][B]$[/B][/COLOR]" & lFirst & ":F[COLOR=#ff0000][B]$[/B][/COLOR]" & lLast & ")"

If you want to use the existing selected range, rather than a user specified range, just change:

Code:
Set rng = Application.InputBox("Please specify range", , , , , , , 8)
'To
Set rng = Selection
 

norts55

Board Regular
Joined
Jul 27, 2012
Messages
144
Stephen, this works great!!!! I am amazed at the knowledge of the users on this forum.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,572
Members
414,389
Latest member
MarkElla

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