Trouble with Formula in Cell
 

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.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Trouble with Formula in Cell
 

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



"Chaplain Doug" wrote in
message
...
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.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Trouble with Formula in Cell
 

Worked like a charm Chip. Thanks and God bless.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org


"Chip Pearson" wrote:

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



"Chaplain Doug" wrote in
message
...
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.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org