Home Variable Column based on Date
Reply: 0

Variable Column based on Date

user1863 Published in May 24, 2018, 4:01 pm

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





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
    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 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!

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO