Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Remove Dashes VBA - not working

  1. #1
    Join Date
    7th January 2005
    Posts
    217

    Remove Dashes VBA - not working

    Does anyone have any idea why this small piece of code isnt working?
    I found it on the site and tried to apply it to the test file - something's not right....? I've attached the test file. Says subscript out of range.

    VB:
    Sub Replacer() 
         'WillR - www.ozgrid.com/forum
         'Removes - chars from text strings in
         'specified range
        Worksheets("Sheet1").Range("H4:H10").Replace _ 
        What:="-", Replacement:="", _ 
        SearchOrder:=xlByColumns, MatchCase:=True 
    End Sub 
    
    
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Remove Dashes VBA - not working

    Could be because your sheet is called RECONCILIATION and not Sheet1.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th January 2005
    Posts
    217

    Re: Remove Dashes VBA - not working

    duh -- that was it !! THANK YOU VERY MUCH Norie!!
    I forgot that I had to use the caption name of the sheet rather than relying upon the real name as shown within the VBA editor...
    (pretty new to VBA, thanks for the help !!)

    How to do you write the code to accomodate for sheet name changes?( if a user decided to change the name of the sheet from RECONCILICATION to INVENTORY ) as as example.... which would cause the code to suddenly not work--- Is there a dynamic way to code it to look at the VBA Editor sheet name "Sheet1"......or do you just lock it some how to prevent users from having the ability to change sheet names?

    The new code that WILL work:
    VB:
    Sub Replacer() 
         'WillR - www.ozgrid.com/forum
         'Removes - chars from text strings in
         'specified range
        Worksheets("RECONCILIATION").Range("H4:H10").Replace _ 
        What:="-", Replacement:="", _ 
        SearchOrder:=xlByColumns, MatchCase:=True 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Remove Dashes VBA - not working

    Chris

    You could refer to the active worksheet like this.
    VB:
    ActiveSheet.Range("H4:H10").Replace _ 
    What:="-", Replacement:="", _ 
    SearchOrder:=xlByColumns, MatchCase:=True 
    
    
    Or to use the sheets codename.
    VB:
    Sheet1.Range("H4:H10").Replace _ 
    What:="-", Replacement:="", _ 
    SearchOrder:=xlByColumns, MatchCase:=True 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th January 2005
    Posts
    217

    Re: Remove Dashes VBA - not working

    Wonderful !!
    I'll make note of this --- for future use....
    I really appreciate your expertise !!! & expeditious help !

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Remove Dashes And Spaces From String
    By for_peekaboo in forum Excel and/or Access Help
    Replies: 2
    Last Post: April 19th, 2008, 05:03
  2. Replies: 3
    Last Post: September 13th, 2004, 20:18

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
  •  
porno