• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

Excel97 - Enclose formula within =If(ISERROR

I would like to be able to quickly enclose a formula as follows:-
=IF(ISERROR(myformula)=TRUE,0,(myformula))
Is there a way some VBA could do this to a selected range of cells containing myformula?
Thanks
0
BlueFin
Asked:
BlueFin
  • 13
  • 6
1 Solution
 
James ElliottCommented:
It's possible but I think the way you are doing it is quickest.

Thanks

Jell
0
 
James ElliottCommented:
does this help

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If IsError(Target.Value) Then
   Target.Value = 0
   End If
End Sub



0
 
James ElliottCommented:
With the above, you would put the formular in the cells themselves. The VB just checks for errors.

If you wanted to do everything with code then you would put.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If IsError(Target.Value) Then
     Target.Value = 0
   else
     Target.Formular = myformular  
   End if

End Sub

Thanks

Jell
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
BlueFinAuthor Commented:
jell
Thanks for the code but what I was really looking for was a module that would do this:-
for the active cells (i.e. the cells selected) whatever the formula contents (x) are - physically change (x) to
=if(isserror(x)=TRUE,0,(x))
0
 
James ElliottCommented:
You could put this code on a macro or command button.

ActiveCell.Formula = myformular

Is this what you are after.

Let me know

Thanks

Jell


0
 
James ElliottCommented:
If you wanted to do this for a number of cells at once then add the line

Selection.AutoFill Destination:=Range("G5:G13"), Type:=xlFillDefault

("G5:G13") is an example of a range of cells to fill with the formular.

Thanks

Jell
0
 
BlueFinAuthor Commented:
jell
Thanks for the code but what I was really looking for was a module that would do this:-
for the active cells (i.e. the cells selected) whatever the formula contents (x) are - physically change (x) to
=if(isserror(x)=TRUE,0,(x))
0
 
James ElliottCommented:
Why did you repeat your post?

To do the code you request, you need to trigger it somehow.

Either by a button, macro etc.

Which ever you choose, you must then use this script on it.

ActiveCell.Formula = myformula

Where 'myformula' is the formula you require. Ok? This will put 'myformula' into the active cell. Ok?

However, if you require your button to do this to a number of cells simultaneously you could use the following code.

Private Sub Example(ByVal Target As Range)
ActiveCell.Formula = myformula
Selection.AutoFill Destination:=Range(target), Type:=xlFillDefault
End Sub

Ok?

Thanks

Jell


0
 
BlueFinAuthor Commented:
jell thanks for your help - I'm sorry I don't think I have got across my requirement properly.
Say I have
in cell A1 the value 1
in cell A2 the value 2
in cell A2 the value 3
Say I have
in cell B1  =A1/A2 I then copy this formula to B2 and B3
in cell B2  =A2/A3
in cell B3  =A3/A4 the result in this cell is #DIV/0!
I would like to be able to highlight cells B1,B2,B3 run a macro that changes the formulas in B1,B2,B3 to
=If(ISERROR(A1/A2)=TRUE,0,(A1/A2))
=If(ISERROR(A2/A3)=TRUE,0,(A1/A2))
=If(ISERROR(A3/A4)=TRUE,0,(A1/A2))
Perhaps your code above does this - but I can't see how and when I try it it does not seem to work.



0
 
James ElliottCommented:
Give me a few minutes and I'll write it for you.

Thanks

Jell
0
 
James ElliottCommented:
Paste this into a new macro:

Sub Macro1()

ActiveCell.Formula = "=IF(ISERROR(A1/A2)=TRUE,0,(A1/A2))"
Selection.FillDown

End Sub

Note: that this will only work for the given formula.

Thanks

Jell
0
 
BlueFinAuthor Commented:
jell
Great - that's what I'm looking for - but can this be amended to cope with differing formula's without hard coding?
P.S. - re the 2nd post of the same response I've no idea how that happened!!
0
 
James ElliottCommented:
With difficulty. I'll give it a go.

(Might be worth a small points increase!! Wink Wink! ;)
0
 
BlueFinAuthor Commented:
jell
Definitely - I'll double the points to 150
0
 
James ElliottCommented:
Try this on a new macro:

Sub Macro1()
Dim ExForm As String
Dim Charnum As Long
ExForm = ActiveCell.Formula
Charnum = Len(ExForm)
ExForm = Right("" & ExForm, Charnum - 1)
ExForm = "=IF(ISERROR(" & ExForm & ")=TRUE,0,(" & ExForm & "))"
ActiveCell.Formula = ExForm
Selection.FillDown
End Sub

Thanks

Jell
0
 
James ElliottCommented:
Try this on a new macro:

Sub Macro1()
Dim ExForm As String
Dim Charnum As Long
ExForm = ActiveCell.Formula
Charnum = Len(ExForm)
ExForm = Right("" & ExForm, Charnum - 1)
ExForm = "=IF(ISERROR(" & ExForm & ")=TRUE,0,(" & ExForm & "))"
ActiveCell.Formula = ExForm
Selection.FillDown
End Sub

Thanks

Jell
0
 
James ElliottCommented:
sorry for double post!!

The above should now work for any formula.

If you would like an explanation of the code, let me know.

0
 
BlueFinAuthor Commented:
jell - great work just what I wanted. This will make it easy to avoid all those #DIV returns. Many thanks.
P.S. if you have some explanation for the code - it would be useful to help me understand a little about VBA.
0
 
James ElliottCommented:
"Dim ExForm As String"
"Dim Charnum As Long"

The above lines are just declaring to the program that I'm about to use my own variables. In this case I want to use a variable called 'ExForm', I want this to be used as a string(i.e. text) and I want to use 'Charnum', a number.

"ExForm = ActiveCell.Formula"
"Charnum = Len(ExForm)"

The above script is telling the program what I want my variables to contain. The first one is saying let the value of Exform be that of the formula in the selected cell. So ExForm = "=a1/b1". Charnum is counting the amount of characters in Exform. So Charnum = "6". (I'll explain why later)

"ExForm = Right("" & ExForm, Charnum - 1)"

Basically the above code takes the '=' sign out of the formula.(Needed for syntax reasons later) This is the reason why I needed to find out the number of charaters in ExForm. Count from right the amount of characters less one. I.e. include all characters except the first one. So Exform now = "a1/b1".

ExForm = "=IF(ISERROR(" & ExForm & ")=TRUE,0,(" & ExForm & "))"

The above script is simply setting Exform now to = the formula required. You'll see that a1/b1 is replaced with Exform. Getting the idea?

"ActiveCell.Formula = ExForm"

This is saying set the formula in the active cell to Exform.

"Selection.FillDown"

This is saying simply fill down the formula to the rest of the selected area.

Thanks

Jell

P.s. don't worry if you don't understand everything said above. It takes a while and a lot of trial and error. My advice would be to install all the help files possible from the MSOffice cd and give it a good go yourself before posting on this site. It is the only way to learn.

Good luck.

P.s.s Feel free to post a question for me if you want. Simply put as a question title -

Q. for Jell - "The your question title"

I will always try and answer promptly and clearly. (Providing the appropriate amount of points are being offered ;)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now