Random Cycle Count Generator

[mbates5898]

I am wanting to create a random cycle count generator for my warehouses. Currently the warehouses choose what they count, so anyone "crafty" can easily manipulate their accuracy %. I'd like to build a tool that will randomly select the locations that should be counted and then diminish those selected locations from from being counted again until all of the items in the warehouse are counted. So basically my original column of locations will be an ever-decreasing number of eligible locations to be counted. No real clue where to start on accomplishing this. I've been told this shouldn't be difficult, but any help runs dry pretty quick. Several hours of google & youtube searching has lead me here....any help is very much appreciated.

 

[mikerickson]

You could do this.
Put a list of all the occupied locations on Sheet1, column A starting with A3. (Rows 1 and 2 are for headers)
Then run this sub.
A list of the locations to count will appear in column B.
Run it again, a new list of locations will be in column C, etc.

As written, it will have you count a default number of locations (10 %).
To specify how many locations to count. Put either a percentage or a raw number in row 1 of the blank column where the locations will appear.
For the very first one, that would be in B1.

You can add or remove locations from the master list in column A and that will be included in the future.

Sub test()
    Dim rngLocations As Range
    Dim rngNextCount As Range
    Dim rngPreviousCounts As Range
    Dim countHowMany As Long
    Dim i As Long
    With Sheet1.Range("A:A")
        Set rngLocations = Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
    For i = 2 To Columns.Count - 1
        If rngLocations.Cells(1, i) = vbNullString Then
            Set rngNextCount = rngLocations.Cells(1, i)
            Exit For
        End If
    Next i
    With rngNextCount
        With .EntireColumn
            If Val(CStr(.Cells(1, 1).Value)) = 0 Then
                    countHowMany = Int(rngLocations.Rows.Count / 10)
                    .Cells(1, 1).Value = (1 / 10)
                    .Cells(1, 1).NumberFormat = "0 %"
                ElseIf Val(CStr(.Cells(1, 1).Value)) < 1 Then
                    countHowMany = Int(Val(.Cells(1, 1).Value) * rngLocations.Count)
                Else
                    countHowMany = Val(.Cells(1, 1).Value)
            End If
            With .Cells(2, 1)
                .Value = "Count These" & vbLf & Format(Date, "mmm d, yyyy")
                .VerticalAlignment = xlTop
               ' .WrapText = True
                .EntireColumn.AutoFit
                .ColumnWidth = 55
                .WrapText = True
            End With
        End With
        Set rngPreviousCounts = Range(rngLocations, rngNextCount.Offset(0, -1)).Resize(rngLocations.Count)
        With .Resize(rngLocations.Rows.Count, 1)
            .Value = rngLocations.Value
            .Offset(0, 1).FormulaR1C1 = "=RAND()"
            .Resize(, 2).Sort key1:=.Cells(1, 2)
            .Offset(0, 1).FormulaR1C1 = "=COUNTIF(" & rngPreviousCounts.Address(, , xlR1C1) & ",RC[-1])"
            .Resize(, 2).Sort key1:=.Cells(1, 2)
            .Offset(countHowMany, 0).Delete shift:=xlUp
            .Offset(0, 1).EntireColumn.Delete shift:=xlToLeft
            .EntireColumn.AutoFit
        End With
    End With
   
End Sub

 

[mbates5898]

This is great. I added it to my sheet and I am going to give it a run Monday morning. The only piece this doesn't hit is removing the selected location from the master list.

Thanks for the help!

 

[mikerickson]

No, its not supposed to remove the selected item from the master list. But it does take prior selections into account.

This bit of code counts how many times a location has been counted during prior cycle counts.

.Offset(0, 1).FormulaR1C1 = "=COUNTIF(" & rngPreviousCounts.Address(, , xlR1C1) & ",RC[-1])"
.Resize(, 2).Sort key1:=.Cells(1, 2)

The model is.
Run the macro now, count the locations in column B. (Save the changes to the workbook)
In a week, run the macro again, count the locations in column C (save changes)
Next week, run again and count column D...

One feature is that you can add data to the column to the right of the "count these" columns (like variance from POS) and the next running of the macro will account for that and put the next week's selections one column over. There needs to be data in row 3 for the macro to see the column as occupied.

 

[cwh1545]

I just wanted to reply to this old thread to give credit to Mike that answered it. Great code!! Perfectly solved my need for cycle counting our warehouse.

 

[jzamarripa]

Hello how would I adjust the VBA if I would like a column to show total times a part has been counted?