Random Generation from list

[GrayJ83]

Greetings,

I am very green at VBA, so I don't even have a sample code to give.

I want to have two columns of words say [A1:A10] and [B1:B10], randomly select a word in the first column (the A column) and print it in D1, then randomly select a word from the second column the B column) and print it in E1.

And be able to repeat the macro, with out having to delete the printed values in D1 and E1.

Thank you for any help that is given.

 

[MickG]

Place this code in your data sheet module:-
Select "C1" or "E1" for related results from "A1:A10" Or "B1:B10"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("D1:E1")) Is Nothing Then
    Select Case Target
        Case Range("D1"): Target = Cells(Application.RandBetween(1, 10), 1)
        Case Range("E1"): Target = Cells(Application.RandBetween(1, 10), 2)
    End Select
End If
End Sub

 

[GrayJ83]

I apologies, I am assuming the date sheet module is the window where you enter the macro? And it does not seem to save when I click the Run button it shows the Macro window were you create the new macro, with it blank.

 

[GrayJ83]

and I can't seem to edit my post to delete that mess. I was trying to put in a screen shot =\

 

[MickG]

To load the code:-
Right click your data sheet tab, From the dropdown menu, select, "View Code".
VB Code Window appears. Paste the code into this window, Close Vbwindow.
Now when you select either "D1" or "E1" the code should run and a new value from either "A1:A10" or "B1":B10" will appear in the selected cell.

 

[Peter_SSs]

Hi Mick, a couple of comments about your suggested code.
- If you select, say, D1 and get a random value and you decide you want a new random value in that same cell, you have to actually select a different cell and then come back to D1. That would annoy me.
- If the user happens to select D1:E1 (or any multi-cell range that overlaps D1:E1, the code errors.

GrayJ83
Because of the above issues, I would suggest using the Double-Click event instead. To test it you would remove Mick's code and try this, implemented in the same way and then test by double-clicking D1 and/or E1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("D1:E1")) Is Nothing Then
    Randomize
    Cancel = True
    Target.Value = Cells(1 + Int(Rnd() * 10), Target.Column - 3).Value
  End If
End Sub

 

[MickG]

Good idea, Peter!!

 

[GrayJ83]

Guys THANK YOU SO MUCH. amazing what excel can do. I want to find a site that teaches VBA.

 

[Peter_SSs]

Guys THANK YOU SO MUCH.

You are welcome.