none
VBA needed that utilizes replace function RRS feed

  • Question

  • Hi,

     I have this simple VBA that allows me to select my range and put a symbol in front of the data:

    Sub AddtoFront()
    Dim Rng As Range
    For Each Rng In Selection
    Rng.Value = "~" & Rng.Value
    Next Rng
    End Sub

    However, I am looking to expand this in a bit of a different way utilizing VBA. I want select my range and use replace to change the middle 6 digits with a * or letter.  Example test data:

    9999999999999999

    1111111111111111

    2222222222222222

    and make it look like this via VBA:

    999999******9999

    111111******1111

    222222******2222

    My ultimate goal is to create an Excel add in that can easily used.

    I didn't quite understand how to be able to select a range and work with a string to accomplish the goal.

    Any help would be appreciated.


    Scott

    Thursday, March 26, 2015 7:37 PM

Answers

  • Re: "use replace to change the middle 6 digits"

    The followiing uses the "Mid" statement  to replace the characters.
    Don't confuse that with the VBA.Mid function  which operates differently.
    The data area must be selected before running the code.
    '---
    Sub ChangeTheMiddle()
      Dim rng As Range
      Dim rCell As Range
      Dim strText As String
      Const STR_FILLER As String = "******"

     'tell excel what to work on (cells)
      Set rng = Selection.Cells
      For Each rCell In rng
        strText = rCell.Text
        If VBA.Len(strText) >= 12 Then
          Mid(strText, 7, 6) = STR_FILLER
          rCell.Value = strText
        End If
      Next 'rCell
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)

     

    • Marked as answer by solo685 Friday, March 27, 2015 3:26 PM
    • Edited by James Cone Tuesday, October 18, 2016 5:15 PM
    Thursday, March 26, 2015 11:35 PM

All replies

  • Re: "use replace to change the middle 6 digits"

    The followiing uses the "Mid" statement  to replace the characters.
    Don't confuse that with the VBA.Mid function  which operates differently.
    The data area must be selected before running the code.
    '---
    Sub ChangeTheMiddle()
      Dim rng As Range
      Dim rCell As Range
      Dim strText As String
      Const STR_FILLER As String = "******"

     'tell excel what to work on (cells)
      Set rng = Selection.Cells
      For Each rCell In rng
        strText = rCell.Text
        If VBA.Len(strText) >= 12 Then
          Mid(strText, 7, 6) = STR_FILLER
          rCell.Value = strText
        End If
      Next 'rCell
    End Sub
    '---

    Jim Cone
    Portland, Oregon USA
    free & commercial excel programs
    https://goo.gl/IUQUN2 (Dropbox)

     

    • Marked as answer by solo685 Friday, March 27, 2015 3:26 PM
    • Edited by James Cone Tuesday, October 18, 2016 5:15 PM
    Thursday, March 26, 2015 11:35 PM
  • Thank James! Works excellent.  I appreciate your quick response.

    Scott

    Friday, March 27, 2015 3:26 PM