#REF when deleting cells

[Øyvind Granberg]

Hi...

I have a spreadsheet importing data from another spreadsheet in the same
workbook.
Upon deleting cells with the original data in the second spreadsheet, linked
cells in the first get their values destroyed when a #REF is inserted into
the formula.

Is there a way to stop this?

The cells in the originating spreadsheet do not change their references.
They're still named A1, A2 and so on.



--

Vennlig hilsen
Øyvind Granberg

(e-mail address removed)
www.tresfjording.com

 

[Pete_UK]

You might be able to get round it by using INDIRECT or OFFSET rather
than specific cells/ranges in a formula, but it depends what formulae
you are using - can you post some examples?

Pete

 

[Bob Umlas]

If you have a reference to A1 in one workbook and that cell is deleted (not
cleared, but like row 1 is deleted), then you'll have a #REF! error. To
ensure a reference to A1 is ALWAYS a reference to A1, use something like
=INDIRECT("[TheOtherWorkBook.xls]Sheet1!A1")

Bob Umlas

 

[Øyvind Granberg]

Indirect did it!
Offset did not...

Thank you guys!

Is there a way to insert a new function in av formula, and at the same time
insert the " and the ) in the beginning AND at the end...
Guess that's a job for VB, and if it is, forget it...
I wrote some easy Basic in the days of my Sinclair QL back in the late
eighties...
Forgot it all, I guess!!

--

Vennlig hilsen
Øyvind Granberg

(e-mail address removed)
www.tresfjording.com

 

[Gord Dibben]

To add the INDIRECT function to your existing linking formulas.

Sub INDIRECTAdd()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then
If Not cel.Formula Like "=INDIRECT(*" Then
myStr = Right(cel.Formula, Len(cel.Formula) - 1)
MsgBox myStr
cel.Value = "=INDIRECT(""" & myStr & """)"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP

 

[Harlan Grove]

I have a spreadsheet importing data from another spreadsheet in the same
workbook. Upon deleting cells with the original data in the second spreadsheet,
linked cells in the first get their values destroyed when a #REF is inserted
into the formula.

Is there a way to stop this?

....

There's no way to stop this, only work around it.

An alternative to the INDIRECT suggestions others provided, you could
use INDEX. For example, if you import data into worksheet I and want
to refer to that data in formulas in worksheet F, then if cell F!C3
should refer to cell I!A1 once all the rows/columns are deleted in
worksheet I, use a formula like the following in F!C3.

=INDEX(I!$1:$65536,ROWS(C$3:C3),COLUMNS($C3:C3))

This will always refer to cell I!A1 no matter how you insert or delete
rows or columns or even move (CUT and paste) cells in worksheet I.

The partial absolute addresses in this formula mean you could fill it
right and down and have it work like the relative address formula =I!
A1. For example, if you fill the F!C3 formula above in to F!D3 then
fill F!C33 down into F!C44, the cell formulas would become

F!D3:
=INDEX(I!$1:$65536,ROWS(C$33),COLUMNS($C33)) -> I!B1

F!C4:
=INDEX(I!$1:$65536,ROWS(C$3:C4),COLUMNS($C3:C4)) -> I!A2

F!D4:
=INDEX(I!$1:$65536,ROWS(C$34),COLUMNS($C34)) -> I!B2

The added bonus is that these INDEX formulas are nonvolvatile, meaning
they're recalculate ONLY when cells in worksheet I change. Since it
seems likely you'd have a lot of these formulas, you may appreciate
reducing recalc time.

 

[Øyvind Granberg]

Thank you Gord!

I managed to create the macro and it did the job...!!

Thanx!

--

Vennlig hilsen
Øyvind Granberg

(e-mail address removed)
www.tresfjording.com

 

[Don Guillett]

Right click on the modue>remove>no

or
For Each x In ActiveWorkbook.VBProject.VBComponents
ActiveWorkbook.VBProject.VBComponents.Remove x
Next

or
Sub NukeCode()
' Need reference to MS VBA Extensibility
Dim VBComp As VBComponent
For Each VBComp In ThisWorkbook.VBProject.VBComponents
Debug.Print VBComp.Name, VBComp.Type
If VBComp.Type = 100 Then
VBComp.CodeModule.DeleteLines 1, VBComp.CodeModule.CountOfLines
Else
ThisWorkbook.VBProject.VBComponents.Remove VBComp
End If
Next
End Sub

 

[Gord Dibben]

Paul

See Debra's site for how-to.


Gord

That explains why I keep getting such messages on macroless spreadsheets
- but how do you remove empty modules (Excel 2003)?

 

[Paul Hyett]

Right click on the modue>remove>no

Thank you - I finally got rid of the annoying 'enable/disable macros'
pop-up that's been plaguing one spreadsheet for years!

 

[Harlan Grove]

That explains why I keep getting such messages on macroless spreadsheets

....

One way to avoid this in future is to use another workbook to hold
utility macros. For example, workbook W.XLS is the one you want to
change using macros. Create a workbook U.XLS, create VBA modules in
U.XLS, switch back to W.XLS and run the macros in U.XLS. Better to use
a different workbook than Personal.xls for ad hoc modules.

 

[moily]

VERY interesting and helpful - thank you lots!

Just for info Moily

After you delete a macro you must also remove the empty module or you will
continue to get the "enable macros?" message.


Gord