Announcement

Collapse
No announcement yet.

Remove Dashes VBA - not working

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Code:
    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

  • #2
    Re: Remove Dashes VBA - not working

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

    Comment


    • #3
      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:
      Code:
      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

      Comment


      • #4
        Re: Remove Dashes VBA - not working

        Chris

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

        Comment


        • #5


          Re: Remove Dashes VBA - not working

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

          Comment

          Working...
          X