Substitute a value from a Choice of multiple words in a offset cell - Randomly

[dj44]

Good day,

i am investigating something to do with substitute.

I wanted to see if this is possible

How may i randomly substitute a XX in a cell with a choice from the offset column

    Sub Substitute_words()
    
    Dim oCell As Range
    Dim i As Long
    Dim vOffset As Variant, vSearch As Variant
     
     
    
    vSearch = Array("XX", "YY")

    vOffset = Array(2, 3) ' Offset  Columns
    

    
    For i = LBound(vSearch) To UBound(vSearch)

    For Each oCell In ThisWorkbook.Worksheets("Test").Range("A2:A4").Cells
    
    Call oCell.Replace(vSearch(i), oCell.Offset(, vOffset(i)).Value, xlPart)
    
    
    Next oCell
    Next i
     
    End Sub

Column A.__________________Column B - Choices
Hello XX___________________Dan| Zoe | Alex
Nice YY___________________Car | Train | Bus

how may i randomly substitute XX or YY with a value from its offset

After

Column A
Hello Dan
Nice Train

would this be possible?

I guess im trying to create some random phrases

I wanted to know how to select a value from the possible values and put that in my XX

Something to do with an random formula i guess

[georgiboy]

How about something like this:

Sub RandomSplit()

    Dim rCell As Range, srcRng As Range, var As Variant
    Dim rndLoop As Long, chVar As Variant
 
    Set srcRng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)


    For Each rCell In srcRng.Cells
        var = Split(rCell.Offset(, 1).Value, "|")
        rndLoop = UBound(var)
        chVar = Split(rCell.Value, " ")
        For x = 0 To UBound(chVar)
            If chVar(x) = "XX" Or chVar(x) = "YY" Then
                chVar(x) = Application.Trim(var(Application.RandBetween(0, rndLoop)))
            End If
        Next x
        rCell.Offset(, 2).Value = Join(chVar)
    Next rCell


End Sub

It will place its result in column C

Hope this helps

[dj44]

Hello gb,

thank you for you response,
sorry for the late reply hectic days
let me do some experimentation