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

smrmodel78

New Member
Joined
May 2, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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


VBA Code:
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

1654694759672.png
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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:

VBA Code:
                    wb2.Sheets(cell.Value).Range("N9,N12:N26,N32:N38,N42:N58,N62:N70,N73:N76,N83:N90").Formula = FYTD
 
Upvote 0
Solution
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:

VBA Code:
                    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.

1654698588008.png
 
Upvote 0
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.)

Excel Formula:
=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:

Excel Formula:
=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.
 
Upvote 0
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.)

Excel Formula:
=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:

Excel Formula:
=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!!!!
 
Upvote 0
You're welcome!

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

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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