Macro to Modify "SUMIF" Formula Range

[norts55]

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.

 

[StephenCrump]

Here's one way:

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]

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]

To make the row references absolute:

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:

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

 

[norts55]

Stephen, this works great!!!! I am amazed at the knowledge of the users on this forum.