updating a cells formula using VBA code

Posted by Rees Macleod on September 13, 2000 8:14 PM

I posted this question earlier this month, but no one responded yet. I don't know if it is too hard or too poorly worded. Please let me know if it is; otherwise, it is very immportant to me to find an answer to it...if anyone has any idea how to do it I would really appreciate any ideas. Thanks again. Here is the problem

I have a spreadsheet that has a user defined number of sheets in it. When they specify the number of sheets I then programmaticaly fill in all the formulas and labels in each cell for each sheet. The problem comes in when someone deletes a sheet, since the top sheet adds values from all the sheets in the workbook. Naturally, I get the REF error as the sheet is no longer there.However, I do not want the people to have to adjust the formula. Is there a way to update the formula to remove the REF error in the formula?

here is an example:

If the formula in a cell was this:

=C17+Sheet1!E20+Sheet2!F22+Sheet4!G21

And someone deletes Sheet2

it leaves this:

=C17+Sheet1!E20+#REF!F22+Sheet4!G21

Can I get rid of the +#REF!F22 part that is now causing the error using code that is tripped when the sheet is deleted?

Thank you in advance for any ideas you might have.

Rees

Posted by Luca Mondin on September 14, 0100 12:56 AM

Try this (I've the italian version of EXCEL and so I hope that the formulas are correct)

=C17+if(val.rif(Sheet1!E20);Sheet1!E20;0) + if(val.rif(Sheet2!F22);Sheet2!F22;0) + if(valr.rif(Sheet4!G21);Sheet4!G21;0)

With this you put "0" if not exist the reference.
If you want to use a macro, then we must do another discussion.

I hope to have you helped!

Ciao,
Luca

Posted by Rees Macleod on September 15, 0100 1:00 AM

great! Thanks for the help guys

Posted by Ivan Moala on September 14, 0100 5:11 AM

Try ths code;

Dim Str As String

Sub DeleteFormula_Ref()

Dim oCell As Range
Dim cRng As Range

Sheets("Sheet1").Activate

On Error Resume Next
CheckAgain:
Set cRng = Selection.SpecialCells(xlFormulas, 16)
If cRng Is Nothing Then Exit Sub
For Each oCell In cRng
SearchF (oCell.Formula)
oCell.Formula = Str
Next
Set cRng = Nothing
GoTo CheckAgain
End Sub

Function SearchF(cell)
Dim x As Integer
Dim y As Integer
Dim Func As WorksheetFunction

Const Ref = "#REF!"
Set Func = Application.WorksheetFunction

x = 1
While Str &LT;&GT; "#"
Str = Mid(cell, x, 1)
x = x + 1
Wend
y = x
While Str &LT;&GT; "+" And x &LT; Len(cell)
Str = Mid(cell, x, 1)
x = x + 1
Wend

Str = Func.Substitute(cell, Mid(cell, y - 2, x + 2), "", 1)
SearchF = Str
End Function

To automate this when sheet is deleted place this
code in the thisworkbook module;

Option Explicit
Dim shName As String
Dim Avail

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
Avail = Sheets(shName).Range("A1")
If Err.Number &LT;&GT; 0 Then
MsgBox shName & " has been Deleted ...Put your routine here to run?"
Application.Run "DeleteFormula_Ref"
End If
On Error GoTo 0
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
shName = Sh.Name
End Sub

What the above does is to automate the running of
the macro to delete the #REF errors WHEN a user
deletes a sheet. Note take out the msg or change
as required.

Ivan

Posted by david on September 13, 0100 10:33 PM

seems kind of tough. I am up to a challenge if nobody else helps you. Keep in mind no garantee but I am getting pretty good at this stuff and think I could do it. You can email me the workbook.
david.rainey@thermacore.com

If not interested My hint would be to have a macro that searches for #ref and deletes those characters from the string and all others after till it hits a plus sign at least that is my first thought.