Is there a way of recording a macro that will perform the
keystrokes that we do - including editing of cells by
using the "F2" function key.

I am wanting to write a procedure that will edit the
formulae in cells in a workbook ie I want the macro to go
into the cell in a sheet that have formulae in them like
='Sheet1'!A10/'Sheet1'!C10 (say in E10) and replace with
=if(Iserr('Sheet1'!A10/'Sheet1'!C10),0,'Sheet1'!
A10/'Sheet1'!C10).

When trying to do this by recording, the recording seems
to switch off when I go into edit mode. This would be Ok
if I wanted relative copies I can record in "relative
mode", but the formulae I want to edit refer to different
sheets rather than relative references on the same sheet.

Any ideas would be welcome.

TIA
Ron

Is there a way of recording a macro that will perform the
keystrokes that we do - including editing of cells by
using the "F2" function key.

No. Excel records the results of what you do, not necessarily the steps
that you perform.

When trying to do this by recording, the recording seems
to switch off when I go into edit mode.

Right. You can neither record nor run macros when in Edit mode.

In Excel you have to "describe" what you want to happen in programming
terms. For example this may be what you want:


Sub a()
Dim CurrFormula As String
CurrFormula = Mid(ActiveCell.Formula, 2) 'Drop "="
ActiveCell.Formula = "=if(Iserr(" & CurrFormula & _
"),0," & CurrFormula & ")"
End Sub


--
Jim Rech
Excel MVP

Ron,
this code when run will change the formulas in the selection from
=The formula to
=if(iserror(The formula),"",The formula)

Sub Addiserror()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = "=if(iserror(" & _
Replace(cell.Formula, "=", "", 1, 1, vbTextCompare) & _
"),""""," & _
Replace(cell.Formula, "=", "", 1, 1, vbTextCompare) & ")"
End If
Next cell
End Sub

HTH
Cecil

"Ron McCormick" wrote in message
...
Is there a way of recording a macro that will perform the
keystrokes that we do - including editing of cells by
using the "F2" function key.

I am wanting to write a procedure that will edit the
formulae in cells in a workbook ie I want the macro to go
into the cell in a sheet that have formulae in them like
='Sheet1'!A10/'Sheet1'!C10 (say in E10) and replace with
=if(Iserr('Sheet1'!A10/'Sheet1'!C10),0,'Sheet1'!
A10/'Sheet1'!C10).

When trying to do this by recording, the recording seems
to switch off when I go into edit mode. This would be Ok
if I wanted relative copies I can record in "relative
mode", but the formulae I want to edit refer to different
sheets rather than relative references on the same sheet.

Any ideas would be welcome.

TIA
Ron

Thanks a million Jim,

Your solution seems absolute magic! And yet it seems so
wonderfully simple - makes me wonder why I hadn't thought
of it. That's probably why you're MVP!

Thanks again.

Ron
-----Original Message-----
Is there a way of recording a macro that will perform
the
keystrokes that we do - including editing of cells by
using the "F2" function key.

No. Excel records the results of what you do, not
necessarily the steps
that you perform.

When trying to do this by recording, the recording seems
to switch off when I go into edit mode.

Right. You can neither record nor run macros when in
Edit mode.

In Excel you have to "describe" what you want to happen
in programming
terms. For example this may be what you want:


Sub a()
Dim CurrFormula As String
CurrFormula = Mid(ActiveCell.Formula, 2) 'Drop "="
ActiveCell.Formula = "=if(Iserr(" & CurrFormula & _
"),0," & CurrFormula & ")"
End Sub


--
Jim Rech
Excel MVP


.

That's probably why you're MVP!

Oh, my head! Must stop the swelling!<g

--
Jim Rech
Excel MVP

select ....Tools, Macro, Record New Macro

this should give you similar functionality to Lotus,

select ....Tools, Macro, Stop Recording

when finsihed,
You can then go back to Tools, Macro, Visual Basic and look at th modules to
see the actual VBA code for what you have recorded.

"tquinn" wrote in message
...
Glad to see I searched this thread. I've been trying to get Excel to
record my keystrokes while editing for a couple of years,
unsuccessfully, and nobody else seemed to know why it wouldn't work.

This is one capability of Lotus that I wish Excel had provided. It
isn't very sophisticated, but if you just want to clean up data or do
the same things to a lot of cells using a macro, a quick recorder
capability is very nice. Excel is so strong in so many areas, I'm
surprised Microsoft neglected this.

Are their any aftermarket utilities that do this?

Terry

This is a common "complaint" from Lotus users. They want the recorder to record key strokes,
such as Enter Edit mode, move to the end of the string, backspace 3 times, hit Enter, move down
to the next cell. Excel won't record that. It will record changing the value in a specific cell
to some specific new value, not the fact that you did 3 backspaces.

The OP will have to learn the equivalent VBA commands for the actions he is taking with his
keystrokes. For the above scenario, it would be

With ActiveCell
.Value = Left$(.Value, Len(.Value) - 3)
End With


On Tue, 02 Sep 2003 02:33:41 GMT, "Dean Waugh" wrote:

select ....Tools, Macro, Record New Macro

this should give you similar functionality to Lotus,

select ....Tools, Macro, Stop Recording

when finsihed,
You can then go back to Tools, Macro, Visual Basic and look at th modules to
see the actual VBA code for what you have recorded.

"tquinn" wrote in message
...
Glad to see I searched this thread. I've been trying to get Excel to
record my keystrokes while editing for a couple of years,
unsuccessfully, and nobody else seemed to know why it wouldn't work.

This is one capability of Lotus that I wish Excel had provided. It
isn't very sophisticated, but if you just want to clean up data or do
the same things to a lot of cells using a macro, a quick recorder
capability is very nice. Excel is so strong in so many areas, I'm
surprised Microsoft neglected this.

Are their any aftermarket utilities that do this?

Terry

Terry

If you truly mean "editing" as in editing cell contents, Excel will not record
keystrokes while in "edit" mode. Macros will not run while in "edit" mode.

If you just mean "editing" in general as in copying/pasting/moving
data/entering data etc. you can use the Macro Recorder as Dean suggests.

For the first scenario, there may be third-party apps but I am unaware of any.

Gord Dibben Excel MVP XL2002

On Mon, 1 Sep 2003 22:14:42 -0400, tquinn wrote:

Glad to see I searched this thread. I've been trying to get Excel to
record my keystrokes while editing for a couple of years,
unsuccessfully, and nobody else seemed to know why it wouldn't work.

This is one capability of Lotus that I wish Excel had provided. It
isn't very sophisticated, but if you just want to clean up data or do
the same things to a lot of cells using a macro, a quick recorder
capability is very nice. Excel is so strong in so many areas, I'm
surprised Microsoft neglected this.

Are their any aftermarket utilities that do this?

Terry