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
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("
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))
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))
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
Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.
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!!
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.
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.
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".
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
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.
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…
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…