Solved

Excel97 - Enclose formula within =If(ISERROR

Posted on 2002-04-25
19
758 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
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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Wierd issue with missing "FROM" field in Outlook. 7 102
LEFT JOIN Access Query 5 64
Excel 2016: auto-number column of merged cells. 47 57
One Drive Login/Configuration Issue 12 43
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

772 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