Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Editing Macro
Can anyone help with a macro that will edit a formula (i.e. add to th
end of a formula) and then move down a row and edit that formula in th same way? My macro (from simply recording keystrokes) replaces the formula in th second row with the new formula in the first row, but I need to retai the original formula in the second row, just adding to the end of it. This is easy to do in Lotus 123 with the {edit} command, but ther doesn't seem to be a corresponding VBA command. Thanks for any help anyone can offer -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Editing Macro
mmarek
Yes, it can be done with a macro, but hard to write any code without seeing the original formula(s) and what you want added to the end(s). Here is some code for adding an ISERROR trap to all cells with formulas. Perhaps you could adapt, or post more info. 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 & "),""""," & myStr & ")" End If End If Next End Sub Gord Dibben Excel MVP On Mon, 10 May 2004 15:18:54 -0500, mmarek wrote: Can anyone help with a macro that will edit a formula (i.e. add to the end of a formula) and then move down a row and edit that formula in the same way? My macro (from simply recording keystrokes) replaces the formula in the second row with the new formula in the first row, but I need to retain the original formula in the second row, just adding to the end of it. This is easy to do in Lotus 123 with the {edit} command, but there doesn't seem to be a corresponding VBA command. Thanks for any help anyone can offer. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Editing Macro
Thanks.
Here are further details on my dilemna. Cell D4 currently has formula =c4 Cell D5 currently has formula = c6 I would like to change D4 to formula =c4+e4 I would like to change D5 to formula =c6+e5 Here is code that I have tried. Excel winds up placing the origina formula in quote marks within the new formula, which results in #NAME result. Sub CellEdit() Dim myStr As String Dim myStr2 As String Dim cel As Range For Each cel In Selection myStr = (Right(cel.Formula, Len(cel.Formula) - 1)) myStr2 = "RC[1]" cel.Value = "=" & myStr & "+RC[1]" Next End Su -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Editing | New Users to Excel | |||
Need help editing a macro | Excel Discussion (Misc queries) | |||
Editing a macro | Excel Discussion (Misc queries) | |||
Editing a Macro | Excel Discussion (Misc queries) | |||
Formula Editing Macro? | Excel Discussion (Misc queries) |