| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating macro
Hi,
Can anyone write a macro for me so that it takes the current formula I have in a cell and adds a IF function and iserror fuction around it. Here is what I would like: Current formula =A1/B1 after running the macro I would like it to look like this: =if(iserror(A1/B1),0,A1/B1) Thanks for your help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating macro
Try
With ActiveCell .Value2 = "=IF(ISERROR(" & Right$(.Formula, Len(.Formula) - 1) & "),0," & Right$(.Formula, Len(.Formula) - 1) & ")" End With -- HTH Bob "Jamie" wrote in message ... Hi, Can anyone write a macro for me so that it takes the current formula I have in a cell and adds a IF function and iserror fuction around it. Here is what I would like: Current formula =A1/B1 after running the macro I would like it to look like this: =if(iserror(A1/B1),0,A1/B1) Thanks for your help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating macro
Use this line of code to change the active cell formula
ActiveCell.Formula = "=if(iserror(" & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & "),0," & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & ")" This would fix all errors on a the active sheet Sub FixErrors() Dim Cell As Object, Sh As Object For Each Cell In ActiveSheet.UsedRange.Cells If IsError(Cell.Value) Then Cell.Formula = "=if(iserror(" & Right(Cell.Formula, Len(Cell.Formula) - 1) & "),0," & Right(Cell.Formula, Len(Cell.Formula) - 1) & ")" End If Next Cell End Sub -- If this helps, please remember to click yes. "Jamie" wrote: Hi, Can anyone write a macro for me so that it takes the current formula I have in a cell and adds a IF function and iserror fuction around it. Here is what I would like: Current formula =A1/B1 after running the macro I would like it to look like this: =if(iserror(A1/B1),0,A1/B1) Thanks for your help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating macro
Hi,
Try this r = Mid(Range("c1").Formula, 2) Range("c1").Formula = "=IF(ISERROR(" & r & "),0," & r & ")" -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Jamie" wrote: Hi, Can anyone write a macro for me so that it takes the current formula I have in a cell and adds a IF function and iserror fuction around it. Here is what I would like: Current formula =A1/B1 after running the macro I would like it to look like this: =if(iserror(A1/B1),0,A1/B1) Thanks for your help. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating macro
Sub ErrorTrapAdd()
Dim mystr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=IF(ISERROR*" Then mystr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=IF(ISERROR(" & mystr & "),0," & mystr & ")" End If End If Next End Sub Gord Dibben MS Excel MVP On Tue, 6 Apr 2010 10:48:04 -0700, Jamie wrote: Hi, Can anyone write a macro for me so that it takes the current formula I have in a cell and adds a IF function and iserror fuction around it. Here is what I would like: Current formula =A1/B1 after running the macro I would like it to look like this: =if(iserror(A1/B1),0,A1/B1) Thanks for your help. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating macro
I put in the below formula but keep getting a syntax error.
Sub FixErrors() Dim Cell As Object, Sh As Object For Each Cell In ActiveSheet.UsedRange.Cells If IsError(Cell.Value) Then Cell.Formula = "=if(iserror(" & Right(Cell.Formula, Len(Cell.Formula) - 1) & "),0," & Right(Cell.Formula, Len(Cell.Formula) - 1) & ")" End If Next Cell End Sub "Paul C" wrote: Use this line of code to change the active cell formula ActiveCell.Formula = "=if(iserror(" & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & "),0," & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & ")" This would fix all errors on a the active sheet Sub FixErrors() Dim Cell As Object, Sh As Object For Each Cell In ActiveSheet.UsedRange.Cells If IsError(Cell.Value) Then Cell.Formula = "=if(iserror(" & Right(Cell.Formula, Len(Cell.Formula) - 1) & "),0," & Right(Cell.Formula, Len(Cell.Formula) - 1) & ")" End If Next Cell End Sub -- If this helps, please remember to click yes. "Jamie" wrote: Hi, Can anyone write a macro for me so that it takes the current formula I have in a cell and adds a IF function and iserror fuction around it. Here is what I would like: Current formula =A1/B1 after running the macro I would like it to look like this: =if(iserror(A1/B1),0,A1/B1) Thanks for your help. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating macro
I figured out the issue. Is there a way to make that macro only work the
cells selected? That would be more helpful. Thanks - Jamie "Paul C" wrote: Use this line of code to change the active cell formula ActiveCell.Formula = "=if(iserror(" & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & "),0," & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & ")" This would fix all errors on a the active sheet Sub FixErrors() Dim Cell As Object, Sh As Object For Each Cell In ActiveSheet.UsedRange.Cells If IsError(Cell.Value) Then Cell.Formula = "=if(iserror(" & Right(Cell.Formula, Len(Cell.Formula) - 1) & "),0," & Right(Cell.Formula, Len(Cell.Formula) - 1) & ")" End If Next Cell End Sub -- If this helps, please remember to click yes. "Jamie" wrote: Hi, Can anyone write a macro for me so that it takes the current formula I have in a cell and adds a IF function and iserror fuction around it. Here is what I would like: Current formula =A1/B1 after running the macro I would like it to look like this: =if(iserror(A1/B1),0,A1/B1) Thanks for your help. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating macro
Try...
For Each Cell In Selection -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Jamie" wrote: I figured out the issue. Is there a way to make that macro only work the cells selected? That would be more helpful. Thanks - Jamie "Paul C" wrote: Use this line of code to change the active cell formula ActiveCell.Formula = "=if(iserror(" & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & "),0," & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & ")" This would fix all errors on a the active sheet Sub FixErrors() Dim Cell As Object, Sh As Object For Each Cell In ActiveSheet.UsedRange.Cells If IsError(Cell.Value) Then Cell.Formula = "=if(iserror(" & Right(Cell.Formula, Len(Cell.Formula) - 1) & "),0," & Right(Cell.Formula, Len(Cell.Formula) - 1) & ")" End If Next Cell End Sub -- If this helps, please remember to click yes. "Jamie" wrote: Hi, Can anyone write a macro for me so that it takes the current formula I have in a cell and adds a IF function and iserror fuction around it. Here is what I would like: Current formula =A1/B1 after running the macro I would like it to look like this: =if(iserror(A1/B1),0,A1/B1) Thanks for your help. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating macro
For Each cell In Selection
With Cell . Value2 = "=IF(ISERROR(" & Right$(.Formula, Len(.Formula) - 1) & _ "),0," & Right$(.Formula, Len(.Formula) - 1) & ")" End With Next cell -- HTH Bob "Jamie" wrote in message ... I figured out the issue. Is there a way to make that macro only work the cells selected? That would be more helpful. Thanks - Jamie "Paul C" wrote: Use this line of code to change the active cell formula ActiveCell.Formula = "=if(iserror(" & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & "),0," & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & ")" This would fix all errors on a the active sheet Sub FixErrors() Dim Cell As Object, Sh As Object For Each Cell In ActiveSheet.UsedRange.Cells If IsError(Cell.Value) Then Cell.Formula = "=if(iserror(" & Right(Cell.Formula, Len(Cell.Formula) - 1) & "),0," & Right(Cell.Formula, Len(Cell.Formula) - 1) & ")" End If Next Cell End Sub -- If this helps, please remember to click yes. "Jamie" wrote: Hi, Can anyone write a macro for me so that it takes the current formula I have in a cell and adds a IF function and iserror fuction around it. Here is what I would like: Current formula =A1/B1 after running the macro I would like it to look like this: =if(iserror(A1/B1),0,A1/B1) Thanks for your help. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating macro
See my post.
Gord On Tue, 6 Apr 2010 11:37:20 -0700, Jamie wrote: I figured out the issue. Is there a way to make that macro only work the cells selected? That would be more helpful. Thanks - Jamie "Paul C" wrote: Use this line of code to change the active cell formula ActiveCell.Formula = "=if(iserror(" & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & "),0," & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & ")" This would fix all errors on a the active sheet Sub FixErrors() Dim Cell As Object, Sh As Object For Each Cell In ActiveSheet.UsedRange.Cells If IsError(Cell.Value) Then Cell.Formula = "=if(iserror(" & Right(Cell.Formula, Len(Cell.Formula) - 1) & "),0," & Right(Cell.Formula, Len(Cell.Formula) - 1) & ")" End If Next Cell End Sub -- If this helps, please remember to click yes. "Jamie" wrote: Hi, Can anyone write a macro for me so that it takes the current formula I have in a cell and adds a IF function and iserror fuction around it. Here is what I would like: Current formula =A1/B1 after running the macro I would like it to look like this: =if(iserror(A1/B1),0,A1/B1) Thanks for your help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a Macro the right way? | Excel Worksheet Functions | |||
| VBA and creating a Macro | Excel Discussion (Misc queries) | |||
| Creating a New Macro | Excel Discussion (Misc queries) | |||
| Creating a macro | New Users to Excel | |||
| Need Help Creating A Macro | Excel Worksheet Functions | |||
Linear Mode
