Hi All:

I'm going to give a lot of detail in hopes that it'll help get an answer
back; sorry for long post.

I've got a bunch of cells in workbook #2 that have formulas that combine
the values from cells within workbook # 2 *and* from corresponding cells
in workbook #1.

I have code that succesfully prompts the user for the name workbook #1
and then does a search/replace of a dummy workbook name (i.e., abc.xls)
imbedded in all the combining formulas.

Because abc.xls, the dummy workbook name, does not exist, I have leading
apostrophes on all these formulas so Excel doesn't burp by prompting for
abc.xls.

I also have a sub procedure to remove these apostrophes which I've used
in numerous other situations:

Public Sub ApostroRemove()
Dim currentcell As Range
For Each currentcell In Selection
If currentcell.HasFormula = False Then
'Verifies that procedure does not change the
'cell with the active formula so that it contains
'only the value.
currentcell.Formula = currentcell.Value
End If
Next
End Sub

I've narrowed down my errors to when my code is trying to remove the
apostrophes - everything else works fine.

If I run this testing code:

Sub Test_FinalStep()
' and lastly, remove all leading apostrophes
' Application.Goto Sheets("05-06 Low Income").Range("D1:E76")
' ThisWorkbook.Worksheets("05-06 Low Income").Activate
' ActiveSheet.Range("D1:E76").Select
Worksheets("05-06 Low Income").Range("D1:E76").Select
ApostroRemove
' Application.Goto Sheets("05-06 Cost Limits").Range("D1:E59")
' ThisWorkbook.Worksheets("05-06 Cost Limits").Activate
' ActiveSheet.Range("D1:E59").Select
Worksheets("05-06 Cost Limits").Range("D1:E59").Select
ApostroRemove
End Sub

I get my 1st error '1004':

"Select method of range class failed"

and Debug points me to the "Worksheets("05-06 Cost Limits").Range
("D1:E59").Select" line.

Note: The error point to this line *if* I run the code when the "Low
Income sheets is active (visible). Also, all the apostorphes have been
removed from the "Low Income" sheet formulas.

If I run the above code w/ the "Cost Limits" sheet active (visible), the
debug points me to the "Worksheets("05-06 Low Income").Range
("D1:E76").Select" line and no apostrophes havebeen removed from any
formula.


Looking in this newsgroup & doing misc. googles, it was suggested that
maybe something is not "active' that should be, so I've tried two
different scenarios using the above code, 1) using the
"Application.GoTo" lines and 2) using the "ThisWorkbook" and
"ActiveSheet" lines.

Each of these generated the same 2nd error '1004':

"Application-defined or object-defined error"

and debug pointed me to the "currentcell.Formula = currentcell.Value"
line at the bottom of the For Each loop inthe ApostroRemove proc.

Note: Both attempts (i.e., using the Application.GoTo" and the
"ThisWorkbook/ActiveSheet") resulted exactly the same.

It also didn't matter which sheet was active when I ran the code,

In all cases all apostrophes have been removed from the "Low Income"
sheet formulas *and* also from the first cell that had an apostrophe on
the "Cost Limits" sheet.

....

thats all I can think of to try at this point ....

Anybody: any ideas/advice?

Worse case, is that I completely do away w/ the dummy workbook name in
all the imbedded formulas methodologt, and after prompting the user for
the name of workbook #1, populate all the cells directly w/ constructed
string values.

I'd prefer, however, to figure this bugger out ...

Thanks in advance for any help/ideas,

Out