Results 1 to 4 of 4

Thread: Replacing VbTab with Underscore in ActiveSheet

  1. #1
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14

    Replacing VbTab with Underscore in ActiveSheet

    I have the following code - But I get runtime error 13 - type mismatch.

    What I am trying to do is replace any VbTab character in any cell with an underscore in my ActivSheet. The code can be completely different from my approach - it does not work anyway.

    Code:
    Private Sub ReplaceVbTabWithUnderscore()
        'Makes sure that the individual cells dont contain a VbTab
        'VbTab is used as delimiter for keys in the TreeView
        'Any VbTab in a cell is replaced with an _ (underscore)
        Dim RangeVal As String
        With ActiveSheet.UsedRange
            LastRow = .Rows(.Rows.Count).Row
        End With
        With ActiveSheet.UsedRange
            ColLast = .Columns(.Columns.Count).Column
        End With
        RangeVal = "A1:" & Split(Cells(1, ColLast).Address, "$")(1) & LastRow
        ActiveSheet.Range(RangeVal) = Replace(ActiveSheet.Range(RangeVal), vbTab, "_")
    End Sub
    Last edited by Rasm; 04-10-2011 at 06:50 PM. Reason: typo
    xl2007 - Windows 7
    xl hates the 255 number

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Replace the last line with
    Code:
     
    Call ActiveSheet.Range(RangeVal).Replace(vbTab, "_")

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Rasm,

    This works for me.

    Code:
    activesheet.usedrange.Replace chr(9),"_",2
    Kris

  4. #4
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    You mean replace entire code with that one line - sweeetttt - it works - tyvm:D
    xl2007 - Windows 7
    xl hates the 255 number

Similar Threads

  1. Trim Text after 3rd Underscore but retain format
    By trankim in forum Excel Help
    Replies: 4
    Last Post: 05-13-2012, 10:44 AM

Posting Permissions

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