Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scan and edit document in excel 2003. | Excel Discussion (Misc queries) | |||
lose formatting (border) in excel cell after pasting from word | Excel Discussion (Misc queries) | |||
I have typed in an excel cell and cannot advance to next cell by . | Excel Discussion (Misc queries) | |||
How to switch to edit the cell | Excel Discussion (Misc queries) | |||
Excel: how to formulate conditional cell references | Excel Worksheet Functions |