Macro to change the formula in a column isn't working

Question

I have a workbook with three tabs for project budget template. There is a drop down menu in the first tab (first tab is called SOW) which calls different macros depending on what is selected from the drop down menu. One of said macros, and the macro which I need to fix, changes the formula for the unit rate in Column G of the second tab (this tab is called Services) depending on which type of template is selected from the drop down box.

This is the formula I need to Company A: =SUMIF(K368:411,C29, L368:L411)

This is the formula I need for Company B: =IF(C429="Managing Director",295,IF(C429="Director of Production",239.5,IF(C429="Deputy Director of Production",225,IF(C429="Executive Producer",215,IF(C429="Senior Producer",185,IF(C429="Producer",149.5,IF(C429="Associate Producer",129.5,IF(C429="Senior Project Coordinator",119.5,IF(C429="Project Coordinator",95,IF(C429="Production Assistant",75,IF(C429="Head of Technical Services",239.5,IF(C429="Technical Director",195,IF(C429="Senior Production Manager",185,IF(C429="Production Manager",149.5,IF(C429="Executive Creative Director",335,IF(C429="Group Creative Director",315,IF(C429="Creative Director",285,IF(C429="Associate Creative Director",185,IF(C429="Design Director",185,IF(C429="Senior 3D Director",179.5,IF(C429="3D Designer",115,IF(C429="Senior 2D Designer",179.5,IF(C429="2D Designer / Production Designer",115,IF(C429="Animator",179.5,IF(C429="Strategy Director",315,IF(C429="Senior Strategist",185,IF(C429="Strategist",165,IF(C429="Group Account Director",265,IF(C429="Account Director",235,IF(C429="Senior Account Manager",195,IF(C429="Account Manager",145,IF(C429="Account Executive",115,IF(C429="Content Designer / Presentation Graphics",175,IF(C429="Lighting Designer",195,IF(C429="Sound Designer",195,IF(C429="Video Designer",195,IF(C429="Showcaller",195,IF(C429="Assistant Stage Manager",175,IF(C429="Makeup Artist",175,IF(C429="Prompt Operator",175,0))))))))))))))))))))))))))))))))))))))))

When I test out these macros to change the unit rate back and forth individually, they seem to work without a problem. However, when they are included in the drop down macro, they mess things up for some reason. There is always one type of role entered into each row of Column G in the Services tab (column G of the second tab). This macro shouldn't enter any values into column G, it should only change the formula in this column which calculates the unit rate. For the SUMIF formula, there is a table with the roles and rates hidden at the bottom of the second tab. For the nested IF formula, I also hid this in one cell at the bottom of the second tab (the macro copies this cell, then selects all necessary rows in Column G in the second tab, and pastes in the nested if formula. The format for column G also seems to change. I have to re-enter all borders for each cell in this column after deleting the role which was entered incorrectly by the macro. Also, in case it makes a different, there are about 16 merged cell rows within said column G which is why there are multiple ranges selected in column G. I was able to get the code below by recording a macro so I'm confused as to what is going wrong. Perhaps I just need to add more to the macros which change the unit rate but wanted to check here in case I am missing something.

 ```UnitRatesForRolesInServicesTab()

    Sheets("SERVICES").Select
    Range("G29").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(R[339]C[4]:R[382]C[4], RC[-4],R[339]C[5]:R[382]C[5])"
    Range("G29").Select
    Selection.Copy
    Range( _

"G30:G48,G50:G69,G71:G90,G92:G111,G113:G132,G134:G153,G155:G174,G176:G195,G197:G216,G218:G237,G239:G258,G260:G279,G281:G300,G302:G321,G323:G342,G344:G363" _
        ).Select
    Range("G344").Activate
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("SOW").Select
    End Sub```

The following is the macro to undo the above:

```Sub UndoUnitRatesForRolesInServicesTab()
'
' UndoUnitRatesForRolesInServicesTab Macro
'

'
    Sheets("SERVICES").Select
    Range("G423").Select
    Selection.Copy
    Range("G29").Select
    ActiveSheet.Paste
    Range( _
        "G30:G48,G50:G69,G71:G90,G92:G111,G113:G132,G134:G153,G155:G174,G176:G195,G197:G216,G218:G237,G239:G258,G260:G279,G281:G300,G302:G321,G323:G342,G344:G363" _
        ).Select
    Range("G344").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.NumberFormat = "0;-0;-;@"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndSha```

Answer 1

This isn't a direct answer to your question, but I hope it simplifies your macros and data (and then maybe simplifies your problem too).

With such a wickedly long and deeply nested IF statement, I'm suggesting a change to how you're determining the rate for a person's role. Using the VLOOKUP formula is a quick way to achieve what you want.

So create another worksheet. My example calls is SupportingInfo. You can make this a hidden worksheet or not. Enter your roles and rates, then convert that data into a table and name it RolesAndRates:

Now on your main worksheet where you're listing all your roles and you need the rates, it's a simple lookup statement:

Notice also that you can add or delete roles or change any of the rates and it will automatically be reflected because of your VLOOKUP formula.

While this isn't a direct answer to your question, I think it will make how you've constructed your worksheet a bit easier.