Solved

Excel97 - Enclose formula within =If(ISERROR

Posted on 2002-04-25
19
752 Views
Last Modified: 2012-06-27
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
Comment
Question by:BlueFin
  • 13
  • 6
19 Comments
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
It's possible but I think the way you are doing it is quickest.

Thanks

Jell
0
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
does this help

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



0
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
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
 

Author Comment

by:BlueFin
Comment Utility
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
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
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
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
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
 

Author Comment

by:BlueFin
Comment Utility
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
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
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
 

Author Comment

by:BlueFin
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
Give me a few minutes and I'll write it for you.

Thanks

Jell
0
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
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
 

Author Comment

by:BlueFin
Comment Utility
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
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
With difficulty. I'll give it a go.

(Might be worth a small points increase!! Wink Wink! ;)
0
 

Author Comment

by:BlueFin
Comment Utility
jell
Definitely - I'll double the points to 150
0
 
LVL 12

Accepted Solution

by:
James Elliott earned 150 total points
Comment Utility
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
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
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
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
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
 

Author Comment

by:BlueFin
Comment Utility
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
 
LVL 12

Expert Comment

by:James Elliott
Comment Utility
"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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now