Automatically Changing Functions
I have over 720 functions across four worksheets that commonly results in #Div/0 errors. I want to insert an "error handling" function to make those #Div/0 errors disappear. That's the easy part. I am using: =IF(ISERROR(OriginalFormula),"",OriginalFormula) -- "Original Function" could be as simple as: =((A1+A5)/(B1-B8)) My problem is that I don't want to manually go to each cell and cut, paste, cut, paste, etc. to make the alterations to each formula. Is there any macro that will make each "OriginalFormula" a variable, and automatically paste the variable (original function) back into my desired new function above? Thanks.... -- bmstar ------------------------------------------------------------------------ bmstar's Profile: http://www.excelforum.com/member.php...o&userid=36264 View this thread: http://www.excelforum.com/showthread...hreadid=565888 |
Automatically Changing Functions
bmstar wrote: I have over 720 functions across four worksheets that commonly results in #Div/0 errors. I want to insert an "error handling" function to make those #Div/0 errors disappear. That's the easy part. I am using: =IF(ISERROR(OriginalFormula),"",OriginalFormula) -- "Original Function" could be as simple as: =((A1+A5)/(B1-B8)) My problem is that I don't want to manually go to each cell and cut, paste, cut, paste, etc. to make the alterations to each formula. Is there any macro that will make each "OriginalFormula" a variable, and automatically paste the variable (original function) back into my desired new function above? Thanks.... Hi bmstar, this worked for me... Public Sub ChangeToHandleError() Dim rngCell As Range Dim strFormula As String For Each rngCell In Application.Selection If Left(rngCell.Formula, 1) = "=" Then strFormula = Right(rngCell.Formula, Len(rngCell.Formula) - 1) rngCell.Formula = "=IF(ISERROR(" & strFormula & _ "),""""," & strFormula & ")" End If Next End Sub It works on the range of cells that you select before running. Try it out on a copy of your sheet first. Ken Johnson |
Automatically Changing Functions
Hi bmstar, just a minor improvement so that the code doesn't change any formulas in the selected range that are already set up to hide the DIV0 error, which results in unnecessarily long formulas... Public Sub ChangeToHandleError() Dim rngCell As Range Dim strFormula As String For Each rngCell In Application.Selection If Left(rngCell.Formula, 1) = "=" _ And Left(rngCell.Formula, 11) < "=IF(ISERROR" Then strFormula = Right(rngCell.Formula, Len(rngCell.Formula) - 1) rngCell.Formula = "=IF(ISERROR(" & strFormula & _ "),""""," & strFormula & ")" End If Next End Sub Ken Johnson |
All times are GMT +1. The time now is 03:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com