{key = 'random name generator list'; text = ''SOLVED' How do I create a random name generator with VBA?';

Hey everyone,

I'm brand new to VBA and Excel but have been trying for hours to figure this out. Any help would be greatly appreciated!

Here is what I want the macro to do:

1) When you press a button, it should randomly select an item from columns A:Z (depending on how many names are in that column). The selected value will then appear in cell O2.

2) After selecting the name, it should clear all values out of cells O3:O10000.

Here is my current code so far:

```

Sub Button1_Click()

Dim lastRow As Long

Range('A1:XFD' & '99999').ClearContents

With Sheets(1).ListObjects.Add _

Name:=Sheet1.ListObjects(Sheet2.Cells(3, 4)), _

Source:=Sheet2.Range('A:A'), _

Header:=xlHeaderCaption, _

AutoExpand:=True

.AutoFit

End With

lastRow = Cells(Rows.Count + 1).End(xlDown).Row - 1

Range('O3:O10000').ClearContents

For i = 2 To lastRow

randomIndex = Application.WorksheetFunction.RANDBETWEEN(1, 10)

If Not IsNothing(Sheet1.Cells(randomIndex + 'A' & i - 1).Value) Then

Range('O3:O10000').Cells(i - 2).Select.Value = _

WorksheetFunctions.Index(Sheet1.Range('Sheet4!B:B'), randomIndex)

End If

End Sub

```

I've tried searching online but can't seem to find anything that exactly matches what I'm trying to do. Any help would be greatly appreciated!

Edit: Here is the error message I keep getting:

```

Run-time error '9': Object variable or With1 object was not set.

```

Random name generator list