+ Reply to Thread
Results 1 to 12 of 12

VBA Macro Replace Entire column

  1. 07-25-2011, 02:30 PM #1
    dwr0211
    dwr0211 is offline
    Registered User
    Join Date
    07-25-2011
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2003
    Posts
    29

    VBA Macro Replace Entire column

    I am new to Excel VBA macros.

    Is there a way to change an entire column where the cell contents are changed to have only the last 5 characters of its existing value?

    Example
    Column A Before
    123456789
    234567890
    345678901

    Column A After
    56789
    67890
    78901

    I have tried the Macro =RIGHT(A:A,5) and received a circular reference error
    Reply With QuoteRegister To Reply
  2. 07-25-2011, 02:35 PM #2
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA Macro Replace Entire column

    Try this:

    Please Login or Register  to view this content.

    Dom
    Last edited by Domski; 07-25-2011 at 02:48 PM.
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
    Reply With QuoteRegister To Reply
  3. 07-25-2011, 02:44 PM #3
    dwr0211
    dwr0211 is offline
    Registered User
    Join Date
    07-25-2011
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA Macro Replace Entire column

    Thank you for your replay.
    I tried this code and received a syntax error. Not sure what is not correct.
    Reply With QuoteRegister To Reply
  4. 07-25-2011, 02:47 PM #4
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA Macro Replace Entire column

    Sorry, slip of the keys. Have amended it.
    Reply With QuoteRegister To Reply
  5. 07-25-2011, 03:07 PM #5
    dwr0211
    dwr0211 is offline
    Registered User
    Join Date
    07-25-2011
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA Macro Replace Entire column

    ok, cool!
    It worked for the most part. I have a new column updated, the K column. Column A remained the same though.
    Is there a way to replace column A with the new values?

    Thank you for the amendment. Just about there!

    I am also new to forum... Is there a way to refresh so I see new posts? Currently I am backing out and coming back in to see latest posts. Or am I just impatient and they show automatically if I wait?
    Last edited by dwr0211; 07-25-2011 at 03:09 PM.
    Reply With QuoteRegister To Reply
  6. 07-25-2011, 03:11 PM #6
    dwr0211
    dwr0211 is offline
    Registered User
    Join Date
    07-25-2011
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA Macro Replace Entire column

    Is the Offset(, 10) putting it in Col K?

    yay... it is the offset!! I changed the Offset to .Offset(, 0) and it replaced the column A values. Thank you for your help!
    Last edited by dwr0211; 07-25-2011 at 03:22 PM. Reason: SOLUTION!
    Reply With QuoteRegister To Reply
  7. 07-25-2011, 03:11 PM #7
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: VBA Macro Replace Entire column

    another way of writing the same thing:

    Please Login or Register  to view this content.

    Reply With QuoteRegister To Reply
  8. 07-25-2011, 03:16 PM #8
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA Macro Replace Entire column

    Yeah, not doing very well. I meant 1 to put it in the next column.

    To overwrite:

    Please Login or Register  to view this content.

    Dom
    Reply With QuoteRegister To Reply
  9. 07-25-2011, 03:23 PM #9
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: VBA Macro Replace Entire column

    @Domski

    I fear that won't do...

    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Last edited by snb; 07-25-2011 at 03:28 PM.
    Reply With QuoteRegister To Reply
  10. 07-25-2011, 03:26 PM #10
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA Macro Replace Entire column

    I should get off my backside and stop typing this on my phone.
    Reply With QuoteRegister To Reply
  11. 07-25-2011, 04:29 PM #11
    dwr0211
    dwr0211 is offline
    Registered User
    Join Date
    07-25-2011
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: VBA Macro Replace Entire column

    Thank you for your responses. When I changed the offset to 0 Dom's code worked.
    I am still looking for the refresh "button" to see new posts that were added. Is there such a button to click?
    Reply With QuoteRegister To Reply
  12. 07-25-2011, 04:31 PM #12
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: VBA Macro Replace Entire column

    Use F5 on your keyboard
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1