Home Variable Column based on Date
Reply: 1

Variable Column based on Date

Jared Nelson
1#
Jared Nelson Published in 2017-11-14 21:48:59Z

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!

Eliot
2#
Eliot Reply to 2017-11-14 22:32:26Z

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.346515 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO