simple key stroke macros in excel

[AndyZ]

I used to use macros all the time in lotus 123. you could record and edit strokes in a cell, or with a little practice you could type in the key strokes.

I recently tried to record a key stroke macro in excel and found it did not work. in 123, my macro would look something like this {edit}{home}{delete}{enter} and this could be activated by a control+letter combination that I set in the spreadsheet.

I have a spread sheet which I have to insert a column of numbers every month to track that months data. In order to make things simple I created a dummy column with the formulas I need. Each cell has a an apostraphe ( ' ) in front of the formula so that excel treats the cell as a text value. to activate the formula I strip out the apostraphe. currently I press F2 to edit the contents of the cell; press home to put the cursor in the same place as the apostraphe and press delete then enter. Presto chango I have a formula instead of a text label. there are about 20 cells in the column, in lotus, 20 repetitions of control something, no thought involved, in excel cumbersome keystrokes. My employer only allows excel.

The macro recorded by excel blew up, exel had no clue what I wanted. The recording was in VB (?).

Any thoughts.

 

[IRstuff]

Sorry you're having so much trouble, but VBA is considerably more powerful than either 123's macro language or even Excel 4's macro language.

As such, unfortunately, you need to be thinking like a programmer, as it is a full blown programming language and there is no longer the constructs that you are familiar with.

The following, while looking odd, takes advantage of the fact that VBA ignores leading quotes:

Sub Macro1()
ActiveCell.Formula = ActiveCell.Formula
End Sub
TTFN

 

[jproj]

AndyZ:

The VBA code required to complete your tast is very simple (thanks to IRstuff's post )... Once you have the formulas in the column you want them in, activate the first cell in the column and execute the following procedure:

Sub ChangeToFormula()
For i = 1 To 20
ActiveCell.Formula = ActiveCell.Formula
ActiveCell.Offset(rowoffset:=1, columnOffset:=0).Activate
Next i
End Sub

If you want to assign a keystroke to the macro (Excel 2000), go to 'Tools' -> 'Macro' -> 'Macros...' then click on the macro name (in this case 'ChangeToFormula') and click on the 'Options...' button. This will let you assign a keystroke to your macro (Crtl + any letter). Beware that any letter you enter will take precedence over any of excel's standard keystrokes (example... if you enter 'b', you can no longer use Ctrl+b to bold the selected text / cell).

Good Luck!

jproj

 

[cpretty]

Why use macros when you can utilise existing features in excel.

Select the area concerned, then use replace (CTRL + H), with the apostraphe ( ' ) in the find variable, and leave the replace variable blank, then when selecting replace all, all the apostraphes will be removed.

Hope this is heaps easier than learning how to use VB

 

[AndyZ]

Thanks for the help, Ctrl+H sounds good. It works for inserting the apostraphe's but I can't get it to strip them. The search criteria only seems to look in formulas and values, not in labels. I am using XP, I don't know if it is a version change, or I just haven't used the replace feature correctly.

As for programming the macro, thanks, I will see if I can make them work. It just seems like a lot of trouble for something that used to be so easy.

I am sure that lotus 123's look and feel lawsuit against microsoft and Borland quatro pro seemed like a good idea at the time, but I would have been much happier if excel had been able to retain the keystroke menu access that 123 developed as an alternative to windows pull down menus.

Thanks for your help. I wish ctrlH worked for me, maybe I can fix that.

 

[IRstuff]

Since the object is to not have an equation, use a double quote instead of the single quote as the first character. CRTL-h will then work. TTFN

 

[AndyZ]

Double Quotes worked like a champ. THANKS.