Type Mismatch error when trying to run macro to copy formulas into another workbook range

[smrmodel78]

I have the following vba macro that set ups my budget file by prompting the user. If the user selects "yes", then it will create total columns and copy the vba formulas in the code. If the user selects "no", then it will just copy the formulas. I keep getting a Type mismatch error (see screenshot) when it begins the for loops to copy the formulas. I can't figure out what I mixed up. Any help is appreciated

Sub CopyYTDFormulas()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim Worksheets As Variant
    Dim my_filename
    
    Set wb1 = ThisWorkbook    'Full Budget Summary Template
    
    '*****************************Open Full Budget File
    my_filename = Application.GetOpenFilename(fileFilter:="Excel Files,*.xl*;*.xm*", Title:="Open Full Budget File")

    If my_filename = False Then
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Set wb2 = Workbooks.Open(my_filename)

    Dim addresses() As String
    Dim addresses2() As String
    Dim lastcol As Long
    Dim i As long, lastytdcol As Long
    Dim tabNames As Range, cell As Range
        
    Const CYTD As String = "=IF(BudgetMonth=""January"",$E10,IF(BudgetMonth=""February"",($E10+$F10),IF(BudgetMonth=""March"",($E10+$F10+$G10),IF(BudgetMonth=""April"",($E10+$F10+$G10+$H10),IF(BudgetMonth=""May"",($E10+$F10+$G10+$H10+$I10),IF(BudgetMonth=""June"",($E10+$F10+$G10+$H10+$I10+$J10),IF(BudgetMonth=""July"",($E10+$F10+$G10+$H10+$I10+$J10+$K10),IF(BudgetMonth=""August"",($E10+$F10+$G10+$H10+$I10+$J10+$K10+$L10),IF(BudgetMonth=""September"",($E10+$F10+$G10+$H10+$I10+$J10+$K10+$L10+$M10),0)))))))))"
    Const FYTD As String = "=(B10+C10+D10)+IF(BudgetMonth=""January"",$E10,IF(BudgetMonth=""February"",($E10+$F10),IF(BudgetMonth=""March"",($E10+$F10+$G10),IF(BudgetMonth=""April"",($E10+$F10+$G10+$H10),IF(BudgetMonth=""May"",($E10+$F10+$G10+$H10+$I10),IF(BudgetMonth=""June"",($E10+$F10+$G10+$H10+$I10+$J10),IF(BudgetMonth=""July"",($E10+$F10+$G10+$H10+$I10+$J10+$K10),IF(BudgetMonth=""August"",($E10+$F10+$G10+$H10+$I10+$J10+$K10+$L10),IF(BudgetMonth=""September"",($E10+$F10+$G10+$H10+$I10+$J10+$K10+$L10+$M10),0)))))))))"
    
    addresses = Strings.Split("N9,N12:N26,N32:N38,N42:N58,N62:N70,N73:N76,N83:N90", ",")
    addresses2 = Strings.Split("O9,O12:O26,O32:O38,O42:O58,O62:O70,O73:O76,O83:O90", ",")
    lastcol = wb2.Sheets("Summary-Current").Cells(5, Columns.Count).End(xlToLeft).Column
    
    On Error Resume Next
    Set tabNames = wb2.Sheets("Summary-Current").Cells(5, 2).Resize(1, lastcol - 1).SpecialCells(xlCellTypeFormulas)
    'actual formula text values on row 5 from column B up to column lastCol'
        
        On Error GoTo 0
        
        If Err.Number <> 0 Then
            MsgBox "No headers were found on row 5 of Summary-Current", vbCritical
            Exit Sub
        End If
    
    Dim answer As String
    answer = MsgBox("Do you need to create CYTD & FYTD Budget Columns?", vbYesNoCancel)
    
    If answer = vbYes Then 'Create Columns & Copy Formulas
         For Each cell In tabNames
              If CStr(wb2.Sheets(cell).Evaluate("ISREF('[" & wb2.Name & "]" & cell & "'!$A$1)")) = "True" Then
              'If wb2 has a tab named for the value in cell
                For i = 0 To UBound(addresses)
                    wb2.Sheets(cell).Range("N").EntireColumn.Insert
                    wb2.Sheets(cell).Range("N6").Value = "FYTD"
                    wb2.Sheets(cell).Range(addresses(i)).Formula = FYTD
                Next i
                
                For i = 0 To UBound(addresses2)
                    wb2.Sheets(cell).Range("O").EntireColumn.Insert
                    wb2.Sheets(cell).Range("O6").Value = "CYTD"
                    wb2.Sheets(cell).Range(addresses2(i)).Formula = CYTD
                Next i
            Else
                Debug.Print "A tab " & cell & " was not found in " & wb2.Name
            End If
        Next cell
    ElseIf answer = vbNo Then 'Copy Formulas
        For Each cell In tabNames
            If CStr(wb2.Sheets(cell).Evaluate("ISREF('[" & wb2.Name & "]" & cell & "'!$A$1)")) = "True" Then
            'If wb2 has a tab named for the value in cell
                For i = 0 To UBound(addresses)
                    wb2.Sheets(cell).Range(addresses(i)).Formula = FYTD
                Next i
                
                For i = 0 To UBound(addresses2)
                    wb2.Sheets(cell).Range(addresses2(i)).Formula = CYTD
                Next i
            Else
            '    Debug.Print "A tab " & cell & " was not found in " & wb2.Name
            End If
        Next cell
    Else
        MsgBox "Cancel"
    End If
    
    Application.CalculateFull
    Application.ScreenUpdating = True

End Sub

 

[6StringJazzer]

cell is declared as a Range. It seems to be complaining about using a Range as in index to Sheets, which must be an integer or a string. Try cell.Value.

Also you do not need a loop to do this. I believe this would work to populate all the cells in one go:

                    wb2.Sheets(cell.Value).Range("N9,N12:N26,N32:N38,N42:N58,N62:N70,N73:N76,N83:N90").Formula = FYTD

 

[smrmodel78]

cell is declared as a Range. It seems to be complaining about using a Range as in index to Sheets, which must be an integer or a string. Try cell.Value.

Also you do not need a loop to do this. I believe this would work to populate all the cells in one go:

                    wb2.Sheets(cell.Value).Range("N9,N12:N26,N32:N38,N42:N58,N62:N70,N73:N76,N83:N90").Formula = FYTD

Thanks Jeff,

That worked perfectly. The formulas copied to each cell, but now I have #REF errors instead of the values from the formula. All the cells in the formula are there. I'm not too experienced with using VBA, but is .Formula correct or should I use .FormulaA1 or .FormulaR1C1.

 

[6StringJazzer]

The formula itself looks OK, do you have a named range BudgetMonth defined in Name Manager?

Do you want these formulas to have relative addressing adjusted? That is, if you assign the formula to row 12 do you want the formula to be

=(B12+C12+D12..........

VBA will not do that automatically for you. The formula in VBA will have to be rewritten to use R1C1 formatting, then assign to .FormulaR1C1.

Also, your formula can be greatly simplified if row 2 month names are strings, not date values. (This is the drawback of showing a picture instead of providing an actual file.)

=SUM(B10:D10, OFFSET(E10,0,0,1,MATCH(BudgetMonth,$E$2:$M$2,0)))

Here is the same formula with relative addressing in R1C1 format:

=SUM(RC2:RC4, OFFSET(RC5,0,0,1,MATCH(BudgetMonth,R2C5:R2C13,0)))

This could also be done for date values, just needs to be a little different.

 

[smrmodel78]

The formula itself looks OK, do you have a named range BudgetMonth defined in Name Manager?

Do you want these formulas to have relative addressing adjusted? That is, if you assign the formula to row 12 do you want the formula to be

=(B12+C12+D12..........

VBA will not do that automatically for you. The formula in VBA will have to be rewritten to use R1C1 formatting, then assign to .FormulaR1C1.

Also, your formula can be greatly simplified if row 2 month names are strings, not date values. (This is the drawback of showing a picture instead of providing an actual file.)

=SUM(B10:D10, OFFSET(E10,0,0,1,MATCH(BudgetMonth,$E$2:$M$2,0)))

Here is the same formula with relative addressing in R1C1 format:

=SUM(RC2:RC4, OFFSET(RC5,0,0,1,MATCH(BudgetMonth,R2C5:R2C13,0)))

This could also be done for date values, just needs to be a little different.

I think I figured out the issue. The file I've been testing in was corrupt. There was a BudgetMonth range that equal #REF. I deleted it and it works perfectly. THANK YOU SO MUCH!!!!

 

[6StringJazzer]

You're welcome!

Regardless of the #REF error, your formula is still way more complicated than necessary.

 

[smrmodel78]

You're welcome!

Regardless of the #REF error, your formula is still way more complicated than necessary.

Thanks. I will look into simplifying the formula.