2 different Run-time Error '1004' ... HELP!!!!!

[List Lurker]

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

 

[Dave Peterson]

You can only select a range on a worksheet's that selected:

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
with Worksheets("05-06 Low Income")
.select
.Range("D1:E76").Select
end with
ApostroRemove
' Application.Goto Sheets("05-06 Cost Limits").Range("D1:E59")
' ThisWorkbook.Worksheets("05-06 Cost Limits").Activate
' ActiveSheet.Range("D1:E59").Select
with Worksheets("05-06 Cost Limits")
.select
.Range("D1:E59").Select
end with
ApostroRemove
End Sub

But you could remove all the .selects and do something like:

Sub Test_FinalStep()
call ApostroRemove(Worksheets("05-06 Low Income").range("D1:E76"))
call apostroremove(Worksheets("05-06 Cost Limits").Range("D1:E59"))
End Sub
Public Sub ApostroRemove(rng as range)
Dim currentcell As Range
For Each currentcell In rng
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

 

[List Lurker]

Hi Dave:

Many thanks for such a quick reply - just finished dinner, sippin' a
nice wine, & sat down at the home computer and and voila' ... there's
your reply.

Alas - tried both and both (your 2nd first, then your "select" version)
and both gen'd the same 2nd error as I originally described.

Debug still points to "currentcell.Formula = currentcell.Value" yet all
apostrophes in the "Low Income" sheet have been removed, yet only yhe
1st cell on the "Cost Limits" sheet.

FWIW, your first suggestion was very similar to what I've read in
similar posts/threads today on a variety of sites, so really thought
that you had nailed it.

What can I say but ...

Que sera, sera!

Again, many thanks for giving it a go.

Looking like I'll have to go w/ brute force, i.e., populated each cell
individually by constructing relevant strings for each formula.

Ciao' for now

 

[List Lurker]

Hi Dave:

It's almost 11PM and the bottle of wine is nearly gone!!!

The reason I'm posting once more (before I crash) is to offer a mea
culpa ....

*Your* code ran bitchen.

*My* workbook had a bad formula (evident after manually removing the
leading apostrophe) ... and guess where it was ..

Yup - the cell *after* the one cell that did resolve OK on the 2nd sheet
when calling the ApostroRemove proc.

It's funny (or sad?) how after many days of putting this thing together,
I transposed a !' as '! ..... in one cell out of some 400+ cells

Anyway, once I spotted that I figured I'd retry my original code, and
BOOM it still didn't work!!! (baastaaard)

Went back & tried yours (where you passs a range to ApostroRemove vs. a
selection) and used your CALL ApostroRemove and it flew!!

I'm now a happpy, albeit exhausted, camper.

Thanks again for your help.

Kudos also to the newsgroup gods - what a resource this is.

Out

 

[Dave Peterson]

Glad you got it working.

In vino, veritas.

(Doesn't quite fit, but if you're still drinking, that shouldn't matter <vvbg>.)