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

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/

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