Trouble with Formula in Cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel 2003. I have code that places within the cell of a "wrap" sheet the
sum of the same cell in all of the other sheets in the workbook. The code
worked fine until I ran into the following case:

The Code:

For Each c In Sh.Range("B9:G32")
If c.Value <> "" Then
MyFormula = "=SUM('" & Sheets(1).Name & ":" & _
Sheets(Lastsheet).Name & "'!" & c.Address & ")"
c.Formula = MyFormula
c.Font.Size = 8
End If
Next

THE PROBLEM:

The line

c.Formula = MyFormula

bombs when MyFormula is:

=SUM('004-Prince George's County, MD:070-Washington, PA'!$B$9)

I know the problem is the apostrophe in George's. What other delimiter can
I use in the SUM besides an apostrophe? Or in what other way may I refer to
the first through the nTH sheet of the workbook without using the sheet
names? THANKS FOR THE HELP. God bless.
 
Doug,

Use two apostrophes in the sheet name. E.g.,

MyFormula = "=SUM('" & Replace(Sheets(1).Name, "'", "''") & ":" &
_
Sheets(LastSheet).Name & "'!" & C.Address &
")"

Watch carefully the mix of single and double quotes.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message
news:[email protected]...
 
Worked like a charm Chip. Thanks and God bless.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


Chip Pearson said:
Doug,

Use two apostrophes in the sheet name. E.g.,

MyFormula = "=SUM('" & Replace(Sheets(1).Name, "'", "''") & ":" &
_
Sheets(LastSheet).Name & "'!" & C.Address &
")"

Watch carefully the mix of single and double quotes.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message
Click to expand...
 
Back
Top