Simple Macro (2003 (11.6355.6408) SP1)

Topic

New Reply

Hello,

I have a list of phone numbers (US), such as:

123-4567

I am trying to create a simple macro that will add an area code to the beginning of the phone number such as “513-” and then move to the cell below. Here is my shot at the macro:

Sub AddAC()
‘
‘ AddAC Macro
‘ Macro recorded 8/8/2005 by Andy LaCombe
‘
‘ Keyboard Shortcut: Ctrl+a
‘
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = “513-bob”
ActiveCell.Offset(1, 0).Range(“A1”).Select
End Sub

All I did was record my steps and I know that this is wrong, as it will make the cells equal 513-bob, instead of adding 513- to bob. I know I need something to select the current cell (something like “with selection”), and then I need to be able to tell Excel to edit the cell, move to the first charcter and then add the desired area code.

I am clueless on how to get there. Any thoughts.

Thanks,

Andy

Reply | Quote

Replies

You only need a small modification:

Sub AddAC()
‘
‘ AddAC Macro
‘ Macro recorded 8/8/2005 by Andy LaCombe
‘
‘ Keyboard Shortcut: Ctrl+a
‘
ActiveCell.Value = “513-” & ActiveCell.Value
ActiveCell.Offset(1, 0).Select
End Sub

The instruction ActiveCell.Value = “513-” & ActiveCell.Value will prefix the current value of the active cell with “513-“.

Reply | Quote

Thanks Hans!

Do you have any sources for more information on simple tasks like this with Excel, and maybe some more advanced stuff?

Thanks,

Andy

Reply | Quote

John Walkenbach’s books are generally excellent – Excel VBA Programming For Dummies is a good introduction, and Excel 2003 Power Programming With VBA goes deeper. See Excel Books by John Walkenbach.

Wordware Publishing: Learn Microsoft Excel 2000 VBA Programming has also been mentioned favorably in this forum (the differences between Excel 2000 and 2003 are not very large).

Reply | Quote

You could also try Writing Excel Macros With VBA by Steve Roman. HTH

Reply | Quote