asked on

How to prevent #REF when sheets are deleted and re-added

I have a very large workbook that imports many worksheets from various places. This is necessary, no linking is possible. A macro runs, deletes the worksheets from the previous day and copies in the new ones. I have several hundred cells that are pulling using a complicated formula, and when the sheets are deleted, it causes the formula to get refilled with a #REF reference to the deleted sheet... even though the sheet is replaced with the same name.

The formula is like this:
=INDEX(MySheetThatGetsDeleted!A:P,MATCH(RptDate,MySheetThatGetsDeleted!B:B,0),MATCH("ASA",MySheetThatGetsDeleted!2:2,0))

So I had this idea of creating my own Intersect Function to feed it the sheet name, the RptDate, and the tag to search for, in this case "ASA". The problem is that I can't use Match in a function.
I had this but it bombs on the Match:
Function ISectPhone(Sheetname, RowTarget, ColTarget) As Variant
Dim sSheetRef As String
Dim sRowRange As String
Dim sColRange As String
sSheetRef = Sheetname & "!A:P"
sRowRange= Sheetname & "!B:B"
sColRange= Sheetname & "!2:2"
ISectPhone = Index(sSheetRef, Match(RowTarget, sRowRange, 0), Match("ASA", sColRange, 0))
End Function

I would call it like this =ISectPhone("MySheetThatGetsDeleted",RptDate,"ASA")

But maybe there is an easier way... let me know any brilliant ideas. One thing I did try was linking the sheets instead of deleting and re-copying.. problem then is it takes a half an hour for the workbook to open. Running the macro to delete and recopy takes only a minute.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Daneke

Just guessing, but what if you turn off automatic recalculation until you finish copying the worksheets?

byundt

You may encounter a problem getting the user-defined function to update when you update the sheets. The standard way is to use Application.Volatile in your UDF. That makes the function recalculate every time anything changes in your workbook. The overhead may be too much (sluggish recalc time), however.

One alternative tactic to consider is to add a parameter to your UDF. This parameter would be used solely to trigger a recalc. In the snippet below, I added the parameter bRecalc. This could be a TRUE/FALSE value in a named range pointing to a cell on a worksheet that won't get deleted. Select that cell, click in the formula bar and hit Enter. All your ISectPhone function calls will now update.

Brad

Function ISectPhone(Sheetname As String, RowTarget As Variant, ColTarget As Variant, bRecalc As Boolean) As Variant
Dim sSheetRef As String
Dim sRowRange As String
Dim sColRange As String
sSheetRef = Sheetname & "!A:P"
sRowRange= Sheetname & "!B:B"
sColRange= Sheetname & "!2:2"
ISectPhone = Application.Evaluate("INDEX(" & sSheetRef & ",MATCH(" & RowTarget & ", " & sRowRange & ", 0), MATCH(""ASA"", " & sColRange & ", 0))")
End Function

Open in new window

Richard Daneke

Turn off calculation and when finished turn on calculation with a full rebuild.

Richard Daneke

Sorry, code snipped was dropped. Here it is and again in Code attached:
Dim oSht as worksheet
Application.Calculation=xlCalculationManual

for each oSht in Worksheets
oSht.enablecalculation=false
'copy to your sheets in here
osht.enablecalculation=true
next osht

Application.calculatefullrebuild

Dim oSht as worksheet
Application.Calculation=xlCalculationManual

for each oSht in Worksheets
oSht.enablecalculation=false
    'copy to your worksheets in here
osht.enablecalculation=true
next osht

Application.calculatefullrebuild

Open in new window

Coghan

ASKER

byundt.. Yes, you are onto something here. Not quite perfect, partially because I had a typo. But now I am getting an N/A error which could mean it just isn't matching. This may be the solution, I'll keep working with it.

byundt

I misspoke when suggesting that you didn't need to change the value of the extra parameter bRecalc. You do in fact need to change its value. The UDF steadfastly resists recalculating until you do so.

Brad

Coghan

ASKER

This is working. I changed the function to XPhone but in the end what I had to do is set up intermediate variables along the way and also I have to feed it the named range for the date with quotations. This now works.

Function XPhone(MySheet, RowTarget, ColTarget) As Variant

Dim sSheetRef As String
Dim sRowRange As String
Dim sColRange As String

sRowRange = MySheet & "!B:B"
sColRange = MySheet & "!2:2"

Dim sMatch1, sMatch2, sIndex

sMatch1 = "Match(" & RowTarget & ", " & sRowRange & ",0)"

sMatch2 = "Match(""" & ColTarget & """, " & sColRange & ",0)"

sMatch2 = Application.Evaluate(sMatch2)

sIndex = "Index(" & MySheet & "!A:P," & sMatch1 & "," & sMatch2 & ")"

XPhone = Application.Evaluate(sIndex)

End Function

Works if I feed it like this: 'note RptDate had to be in quotes.
=XPhone("Total_no_Default","RptDate","ASA")

Coghan

ASKER

I had to monkey a little bit with it, but the basic premise was spot on. Very well done and I can use this same method to simplify more that I do.

byundt

Coghan,
Have you tried changing a value on one of the worksheets? You'll find that your function doesn't update. That may or may not be a problem, but I would be remiss if you didn't salute the issue.

The snippet shows a different approach, using Application.Match and Application.Volatile (to force a recalc)

Thanks for the grade and kind words!

Brad

Function XPhone(MySheet, RowTarget, ColTarget) As Variant

Dim sSheetRef As String
Dim RowRange As Range
Dim ColRange As Range
Dim sMatch1, sMatch2, sIndex
Application.Volatile

Set RowRange = Worksheets(MySheet).Range("B:B")
Set ColRange = Worksheets(MySheet).Range("2:2")

sMatch1 = Application.Match(RowTarget, RowRange, 0)
sMatch2 = Application.Match(ColTarget, ColRange, 0)
XPhone = Worksheets(MySheet).Cells(sMatch1, sMatch2)
End Function

Open in new window

Coghan

ASKER

Good idea but I have to ctrl alt f9 anyway for other reasons. Still I'll fool around with it tomorrow and see what happens. I thought of the Cells thing as well. Next thing I need is to retrieve the actual address from this. Next question.. ha ha.

byundt

With my last code snippet, changing statement 14 to the following would return the address:
XPhone = Worksheets(MySheet).Cells(sMatch1, sMatch2).Address(External:=True)

Coghan

ASKER

I still had to play with a bit, but I did get it working now. I have another question, but I can post in a new box if more appropriate. Now that I have the XPhone part working... I need to know how I can return the Worksheets(MySheet).Cells(sMatch1, sMatch2).Address(External:=True) somehow from the function so that I can hyperlink to that page.

In other words now I have in the cell this: =XPhone("Total_ICAN_Sub_no_Default",RptDate,"ASA")
And that returns exactly what I need it to. And using your suggestion, I was able to get the location of the worksheet so that I can hyperlink to it. I'd like to be able to create a hyperlink based on the formula result sending the person to the value from Worksheets(MySheet).Cells(sMatch1, sMatch2).Address(External:=True) when they hit a function key. Want me to post as a new question?

byundt

I see that you opened a new question in See http:/Q_26111661.html to cover hyperlinking to the cell being referred to by this cell.

FWIW, a user-defined function being used in a worksheet formula is unable (by Microsoft design) to do anything that changes the user interface. Even if you included code in XPhone that would set a hyperlink, it wouldn't execute.

That said, I came up with a workaround using a Worksheet_BeforeDoubleClick event sub in that other thread.

Brad