How can I edit cell contents with a macro in Excel?
 

I'm trying to creat an Excel macro that edits cell data, remembering the
edits, not just the resulting value or formula. If, for example, I want to
delete all but the last three characters of a cell value, and I record a
macro, editing the cell produces a line like: ActiveCell.FormulaR1C1 = "745".
But I don't want the specific value. I want the process.

I'm essentially asking for RIGHT(R1C1,3), but I can't use that formula in
the macro because it would be a circular reference. What am I missing?

Those same kind of functions are built into VBA:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range with Constants"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = "'" & Right(myCell.Value, 3)
Next myCell

End Sub

I made the value a string ("'" & ...)
Then when I had "asdf001", the rightmost 3 characters would be 001--not 1.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

NotAnExpert wrote:

I'm trying to creat an Excel macro that edits cell data, remembering the
edits, not just the resulting value or formula. If, for example, I want to
delete all but the last three characters of a cell value, and I record a
macro, editing the cell produces a line like: ActiveCell.FormulaR1C1 = "745".
But I don't want the specific value. I want the process.

I'm essentially asking for RIGHT(R1C1,3), but I can't use that formula in
the macro because it would be a circular reference. What am I missing?

--

Dave Peterson