Macro recording

  • Thread starter Thread starter Ron McCormick
  • Start date Start date
R

Ron McCormick

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.
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
 
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
 
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
 
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.
 
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
 
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
 
Back
Top