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

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

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