How put "F2" key in a macro?

[George]

Am using MS Excel XP (2002). All I want to do is make a short macro to, in
effect, press F2 (to EDIT the cell), then let's say delete the first
character of the cell and change it to let's say X, like this...

Before:
_Mary Jones
*Joe Smith
%Peter Rabbbit

After:
XMary Jones
XJoe Smith
XPeter Rabbbit

I just want to put the cursor on a given cell, click CNTL-A to run the
macro, and have it change ONE cell AT A TIME. There are reasons why I can't
use the search and replace.

The problem is... if I record the macro (turn it on, press the key
sequences, F2, Home, Delete, X, GoDownOneCell, turn it off), it puts "XMary
Jones" in ALL the cells, like this:

After:
XMary Jones
XMary Jones
XMary Jones

For some reason, I think it has a problem with F2 key. Would appreciate any
suggestions, thanks, George.

 

[papou]

Hello
You can't edit formulas using VBA.
I would suggest you use some macro like this one below:
Sub changefirstletter()
ActiveCell = "X" & Mid(ActiveCell, 2)
End Sub

HTH
Cordially
Pascal

 

[George]

Thanks,

Is there any way at all to have Excel record my keystrokes (including using
things like F2 to edit a cell) and then play them back each time I hit a
macro key (like <Cntl> A)?

A long time ago, when I used Lotus, you just "recorded" ANY set of
keystrokes and gave it a macro name...then used the macro instead of
manually typing the same thing over and over.

Seems like Excel should be capable of this (without getting into macro
language, etc.). If not, I was wondering what the idea behind the "record"
thing is.

Thanks,
George

 

[papou]

George
You can record actions (Tools, Macro, New Macro) and define a shortcut key
to execute them in Excel, but it won't record key strokes used for editing
cells so that is why I suggested this little macro for you.
Although I would not personnally recommend it, there is another possibility:
the SendKeys method in VBA, but obvioulsy you would need to go into some
coding.
HTH
Cordially
Pascal

 

[Earl Kiosterud]

George,

Excel's macros use the VBA language. It isn't keystroke-based. Try:

Sub ChangeX()
ActiveCell = "X" & Right(ActiveCell, Len(ActiveCell) -1)
End Sub

 

[George]

OK, I can tell you guys are way more advanced than this humble business
user...

-Suppose I want to copy that macro code and use it...are there some simple
steps like... Tools > Macro > VBEditor ... and it would show a blank area...
and I just paste it in and click Save or something... then run it with
<Cntl> something? I know I could go off and study macro's, but I'd like to
just know a few starter basics to get going with something I can do in a few
minutes and see it work.

-More importantly.... what most confuses me is that I thought the idea of a
macro was to capture some manual tasks... like keystrokes... without rules
of "as long as you don't edit cells" or "as long as you don't use an F key",
or other restrictions. I know Lotus has long been out of prime time, but it
did do just that... you just recorded the keys you wanted pressed, label it
<Cntl><something>, and it remembered them. Isn't there "any" way Excel
could do that without stepping into VBA.

Oh well, thought I'd ask anyway,
Thanks,
George

 

[papou]

Hello George
You're not far from it!
Tools, Macro, Visual Basic Editor, and in the VB Editor menu:
Insert, Module and then paste the macro onto the blank sheet and close the
Visual Basic Editor.
The new macro will then be available from Tools, Macro, Macros.
As for shortcut keys to execute macros, supposing you have been pasting the
code I supplied :
you will need to go into Tools, Macro, Macros, select the changefirstletter
macro and then click on Options and define the shortcut key there.
To get going with macros, just use the macro recorder and try and understand
the language used.
Finally now as regards recording the keys pressed, this is a long time now
since Microsoft are not using this method (it was used in Multiplan as far
as I recall)
HTH
Cordially
Pascal