.formula has VBA error when using special characters

[tderb001]

I have an excel macro that loops through table body cells on each worksheet and inserts a cell formula using a combination of text strings. There are three worksheets.

The macro works fine on worksheet 1 and 3, but on worksheet 2, the macro errors out with an application-defined or object-defined error. The error goes away when I remove all special characters from the formula text strings.

The special characters I use are ( " and @ . I have tried using the special characters within quotations like "@" and I have tried using the Chr(64) function to insert the special character. The macro works fine in Excel 2016, but it is breaking in Excel 2010. What is going wrong?

 

[Rick Rothstein]

I think you need to show us your code.

 

[tderb001]

HERE IS THE MACRO THAT LOOPS THROUGH EACH SHEET AND RUNS THE MACRO TO UPDATE FORMULAS

Sub UpdateFormulas()
Application.ScreenUpdating = False
For Each xSheet In ThisWorkbook.Worksheets
With xSheet
xSheet.Activate
Set xSheet = ActiveSheet
If xSheet.Name <> "Background" Then
FillFormulaAllSheetTables
End If
End With
Next
Application.ScreenUpdating = True
End Sub


HERE IS THE MACRO THAT RUNS ON EACH SHEET

Sub FillFormulaAllSheetTables()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim xSheet As Worksheet
Dim xTable As ListObject
Dim zCell As Range
Set xSheet = ActiveSheet
For Each xTable In xSheet.ListObjects
With xTable
For Each zCell In xTable.DataBodyRange.Cells
.Formula = "=@cw_mapdesc(" & Chr(34) & zCell.Offset(0, -1 * (zCell.Column - 1)).Value2 & Chr(34) & ")"
Next
End With
Next
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub