Solved

Excel97 - Enclose formula within =If(ISERROR

Posted on 2002-04-25
19
761 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 6
19 Comments
 
LVL 12

Expert Comment

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

Thanks

Jell
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 6968154
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
ID: 6968156
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
Technology Partners: 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!

 

Author Comment

by:BlueFin
ID: 6968195
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
ID: 6968242
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
ID: 6968249
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
ID: 6968774
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
ID: 6968797
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
ID: 6968833
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
 
LVL 12

Expert Comment

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

Thanks

Jell
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 6968891
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
ID: 6968937
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
ID: 6968970
With difficulty. I'll give it a go.

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

Author Comment

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

Accepted Solution

by:
James Elliott earned 150 total points
ID: 6969076
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
ID: 6969087
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
ID: 6969113
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
ID: 6969126
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
ID: 6969380
"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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Outlook Free & Paid Tools
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

756 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