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