0

VBA noobie here.

I have a spreadsheet with a list of cashiers vertically and each day of the month horizontally. i.e:

11/1/2017    11/2/2017    11/13/2017

Jesse

Frank

Jessica

Martin

The rest of the spreadsheet is populated with sales data for each cashier.

I want to randomly select 5 cashiers and display their names and sales data on a separate sheet.

I have code written that will select 5 random names, however I'm looking to make it dynamic so that the user can choose which column to run the macro on (i.e., they can choose which date).

`Sub PickNamesAtRandom()

Dim HowMany As Integer
Dim NoOfNames As Long
Dim RandomNumber As Integer
Dim Names() As String 'Array to store randomly selected names
Dim i As Byte
Dim CellsOut As Long 'Variable to be used when entering names onto worksheet
Dim ArI As Byte 'Variable to increment through array indexes

Application.ScreenUpdating = False

HowMany = Range("C43").Value
CellsOut = 6

ReDim Names(1 To HowMany) 'Set the array size to how many names required
NoOfNames = Application.CountA(Range("A:A")) - 1 ' Find how many names in the list
i = 1

Do While i <= HowMany
RandomNo:
    RandomNumber = Application.RandBetween(2, NoOfNames + 1)
    'Check to see if the name has already been picked
    For ArI = LBound(Names) To UBound(Names)
        If Names(ArI) = Cells(RandomNumber, 1).Value Then
            GoTo RandomNo
        End If
    Next ArI
    Names(i) = Cells(RandomNumber, 1).Value ' Assign random name to the array
    i = i + 1
Loop

'Loop through the array and enter names onto the worksheet
For ArI = LBound(Names) To UBound(Names)

    Cells(CellsOut, 4) = Names(ArI)
    CellsOut = CellsOut + 1

Next ArI

Application.ScreenUpdating = True

End Sub`

Any help is appreciated!

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Jared Nelson
  • 1

1 Answers1

0

You could use an InputBox to get which column the user wants to use.

Dim lRet As Long
lRet = clng(Inputbox("Select column"))

You can then use that variable later in your code. Change where you have this:

Cells(RandomNumber, 1).Value

To this:

Cells(RandomNumber, lRet).Value

Edit: You will also need to change this line:

NoOfNames = Application.CountA(Range("A:A")) - 1

I would suggest adding a function to convert numbers to letters: Function to convert column number to letter?

Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function

Your line should change to:

NoOfNames = Application.CountA(Range(Col_Letter(lRet) & ":" & Col_Letter(lRet))) - 1
Eliot
  • 91
  • 5