Hi Everyone,
I am needing to eliminate the dreaded #DIV/0 error - I am trying to use it in conjunction with this code
This is also returning an error - Says "You have entered too few arguments for this function"
=IFERROR(INDEX(O7:AH19,,MATCH(AI$2,V$4:AH$4,0))-INDEX(O7:AH19,,MATCH(AI$2,V$4:AH$4,0)-1))/INDEX(O7:AH19,,MATCH(AI$2,O$4:AH$4,0)-1),"")
Any Ideas on this issue???
John
Last edited by JJFletcher; 09-06-2015 at 08:59 PM.
I think you just had a misplaced closing ) in there:
=IFERROR(INDEX(O7:AH19,,MATCH(AI$2,V$4:AH$4,0))-INDEX(O7:AH19,,MATCH(AI$2,V$4:AH$4,0)-1)/INDEX(O7:AH19,,MATCH(AI$2,O$4:AH$4,0)-1),"")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony Valko ,
This took away the error - thanks so much...
Is there code that will eliminate the error without hard coding the individual cells for the results?
Good deal!Originally Posted by JJFletcher
Having read the other replies in the thread I don't know why you'd want to do this.Is there code that will eliminate the error without hard coding the individual cells for the results?
However, it can probably be done using a VBA procedure but I'm not much of a programmer so someone else will need to help you with it.
Thanks for your replies.....
This is a piece of code I found - but it does not seem to work very well
The #DIV/0! still propagates the cellsSub DelDiv0() ' Removes the #DIV/0! values from all cells in the selected ' range and replaces them with a zero value Dim Cel As Range For Each Cel In Selection If IsError(Cel.Value) Then If Cel.Value = CVErr(xlErrDiv0) Then Cel.Value = 0 End If Next Cel End Sub
Any thoughts
John
What do you mean?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Is there a way to code the worksheet so that all errors are taken care of if they occur in the cells?
John
If any part of that formula produces an error, the IFERROR will catch it
Agreed, yet the code is entered directly into the cell - I was wondering if a Macro could be created to perform the function without putting it directly into the cell...
Why would you not want to put it in the cell?
To eliminate having to put the code into each cell which is ranging on average 300 cells,
Putting the code into the cell desired to control the error when dividing 0/0 I can do... thought there is possible a Macro that can do the work !
Last edited by JJFletcher; 09-06-2015 at 10:59 PM.
How have used it?
You select a range and then run the macro: it does not automatically resolve the errors.
Last edited by JohnTopley; 09-07-2015 at 03:11 PM.
I would like the code to automatically function when the page loads... the above Macro
is loaded into each cell in the column...=IFERROR((INDEX(O7:AH19,,MATCH(AI$2,V$4:AH$4,0))-INDEX(O7:AH19,,MATCH(AI$2,V$4:AH$4,0)-1))/INDEX(O7:AH19,,MATCH(AI$2,O$4:AH$4,0)-1),"")
You cannot load a macro into a cell.
Hi John,
Thanks for your response...
So a Macro would not be able to locate #DIV/0 in the spreadsheet and cause the cell to be blank?
A #DIV/0! error is the result generated by a formula. If you don't want the formula result to be the #DIV/0! error then you would typically write the formula to account for those errors and return an alternative result.
You said the formula suggested in post 2 solved the #DIV/0! error problem.
If there are no more #DIV/0! errors then why do you need a macro to remove them?
So, what exactly are you wanting to do?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks