Need to replace X in text with A through F

[adammj58]

Good day all,

I am a Excel VBA rookie (to say the least.) I have a file from an external department that uses "X" as a wildcard to represent the characters A through F in a series of part numbers. I have created some code so that the parts that contain an X is listed 6 times in the original column. Now I want to go through that column that contains those parts (in this case, column B) and, for each X, replace with its appropriate character.

For example:

AL4500X03-E6 in rows 9 through 14 in column B becomes:

AL4500A03-E6
AL4500B03-E6
AL4500C03-E6
AL4500D03-E6
AL4500E03-E6
AL4500F03-E6

And then continue on to the next item that contains an X and replace with A through F in each case again.

Appreciate any help you can offer!

Thanks
Adam

[StephenR]

Can you post a sample workbook? Is the X always the 7th character?

[adammj58]

Sure thing. Yes, X is always the 7th character in the part numbers I'm working with. You will see from the sample, not all the parts in column B will have a wildcard X.

[StephenR]

EDIT: made a right pig's ear of that, will be back shortly so amuse yourself.

Right, try this. It could be a made a little shorter if your descriptions are common, but I think this works.

Sub x()

Dim rng As Range, n As Long, r As Long

For Each rng In Sheet1.Range("B2", Sheet1.Range("B2").End(xlDown))
    If Mid(rng, 7, 1) = "X" Then
        Do While rng.Offset(n) = rng
            n = n + 1
        Loop
        For r = 0 To n - 1
            rng.Offset(r) = Left(rng, 6) & Chr(65 + r) & Right(rng, 5)
        Next r
    End If
    n = 0
Next rng

End Sub

[Leith Ross]

Hello Adammj58,

Here is another macro that uses Regular Expressions to find an replace the "X" with the letters you want. A button has been added to the attached workbook to run the macro.

Sub ReplaceX()

  Dim Data As Variant
  Dim N As Integer
  Dim RegExp As Object
  Dim Rng As Range
  Dim RngEnd As Range
  Dim X As Long
  
    Set Rng = Range("B2")
    Set RngEnd = Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Range(Rng, RngEnd))
    
    Data = Array("A", "B", "C", "D", "E", "F")
    
    Set RegExp = CreateObject("VBscript.RegExp")
    RegExp.Pattern = "[xX]"
    
      For Each Cell In Rng
        If RegExp.Test(Cell.Text) Then
           N = X Mod 6
           Cell.Value = RegExp.Replace(Cell.Text, Data(N))
           X = X + 1
        End If
      Next Cell
     
    Set RegExp = Nothing
    
End Sub