Randomly Generate text in cells from a combined range of cells

[sinamon]

Im trying to create a random phrase generator based on text values combined
from different Columns/ rows.

I need it to randomly select one text value from each row/column and combine the selected values and out put to a cell using a command button.

If any one knows a formula or VBA code for this would be great help thanks

Please see attachment example
5936

[BrianMH]

How about a UDF that expands to however many columns you want? It will only currently work on a contiguous range though. Put this in a module in the workbook or better yet in an addin or your personal workbook.


Option Explicit
Public Function randomphrase(wordrange As Range) As String
Dim lRows As Long
Dim lCols As Long
Dim i As Integer
Dim sPhrase As String
Dim lRnd As Long
lRows = wordrange.Rows.Count
lCols = wordrange.Columns.Count
For i = 1 To lCols
lRnd = CLng(lRows) * Rnd() + 1
sPhrase = sPhrase & " " & wordrange.Cells(lRnd, i)
Next
randomphrase = sPhrase
End Function


Then just use it as a formula.

[sinamon]

Forgive me Im a total noob with VBA/Excel
I added a form control and added your code to
the VBE but when I run it I get the errors

sometimes this -compile error ambiguous name detected:randomprhase
sometimes this -compile error Expected End Sub

heres what my code looks like
Sub randomphrase_Click()
Public Function randomphrase(wordrange As Range) As String
Dim lRows As Long
Dim lCols As Long
Dim i As Integer
Dim sPhrase As String
Dim lRnd As Long
lRows = wordrange.Rows.Count
lCols = wordrange.Columns.Count
For i = 1 To lCols
lRnd = CLng(lRows) * Rnd() + 1
sPhrase = sPhrase & " " & wordrange.Cells(lRnd, i)
Next
randomphrase = sPhrase
End Function
heres the file not sure where Im going wrong
5939

[BrianMH]

You dont use it with a click button you use it as a formula. If you really want a click button I'll sort that tomorrow but it won't be as flexible.

[BrianMH]

ok I corrected a bit of the code. I have uploaded a workbook to show how to use the formula. I think it works better if you use the formula but I have added a button that puts the formula in the specified cells if you really want a button.

[sinamon]

Hey thats just what I was looking for, works perfect:bow:

I'm still trying to make since of the two module codes, but either way you rock.

Thanks for your help

[sinamon]

I pretty much figured out your code thanks again but I have another related question. The results of the random text are displayed initially in the cell but when I go to the actual cells it displays the formula,(=randomphrase($A$2:$E$87) so I'm unable to copy the text. How can I have the cell keep the formula but allow me to copy the text or out put the text to an additional row/column to allow me to copy and paste?

Thanks for your assistance

[BrianMH]

Don't double click the cell. Just click the cell and copy then paste.

If you really want it to just use values then this should work.

Sub Button3_Click()

Dim rValues As Range
Set rValues = ThisWorkbook.Sheets(1).Range("G2:G6")
Dim c As Range
For Each c In rValues
c.Formula = "=randomphrase($A$2:$D$6)"
c.Value = c.Value
Next
End Sub