Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple Macro (2003 (11.6355.6408) SP1)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Macro (2003 (11.6355.6408) SP1)

    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-".

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Loveland, Ohio, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Macro (2003 (11.6355.6408) SP1)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple Macro (2003 (11.6355.6408) SP1)

    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).

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple Macro (2003 (11.6355.6408) SP1)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •