Excel/VBA-Random 25% of cells selection in each columns based on specific criteria
Posted on 2004-04-19
Actually I'm working to extend existing Excel Add-ins, so im working with VBA(nice uh?).
Now I try to explain the project that I divide in two part, the first that i have already done and the second where my troubles begins.
What I need is to write a tool that based on specific criteria(ex: depending of the value in the rows) search for a certain values in all the cells for each column (I need to search in the cell for the string "DR1") then for each column, counting how many cells contain this value, then calculate the 25% of the total of cell founded and round it to the next greater value (ex:I found 4 cell containing "DR1" the 25% is = 1).
This was is the frist part of the problem that I have already coded without any great difficulty.
Now comes the second part that I'm stuck with.
So far what I have is something that loops trough the rows and columns, does some checks, add values to a counter, does some math and give me the result.
Now, I know what is the 25% of the result per column of the total of the values founded, but what I need now is using this value as a reference that set the number of cells that have to be selected from the cells founded for each column,but the selection has to be randomized.(ex: I found 8 cell in the column that contain the string "DR1" the 25% is 2, I have to randomly select 2 of these 8 cells in the column), I mean random like the selection has to be the randomized, not simply select the first two.
This is my problem... how to randomize the selections of the cells that have to be selected?This has to be done for each column(counting the cells, calculate the 25%, select at random the number of cell based on the percent.)
I said that I have to select the cells but what I really have to do is not really a selection but I have to leave the original color only for this cells and for the rest of cells (there is other cell with other values inside with different color) assign a grey color, but this is a part of code that I think I can do by myself without problem.
I hope that you guys have understand the problem and same good soul can help me.
Thnx in advance.
- dario -
This is the code that I wrote so far.
CountPatientVisit LastPatientRow, LastVisitColumn
Dim site As String
site = InputBox(prompt:="Enter the name of the site")
If site = "" Then Exit Sub
For j = 3 To LastVisitColumn
If Worksheets("Visit Patterns").Cells(3, j) = "0" Then
rnd100 = j + 1
rnd25 = j + 2
For j = rnd25 To LastVisitColumn
count = 0
For i = 3 To LastPatientRow
If (Worksheets("Summary").Cells(i, "A").Value = site) Then
If Worksheets("Summary").Cells(i, j).Value = "DR1" Then
count = count + 1
'MsgBox Worksheets("Summary").Cells(i, j).Value & " Col: " & j & " - " & count
roundCount = Round(count * 0.25)
MsgBox " The 25% pecent of the total of: " & count & " DR1 in Column: " & j & " = " & roundCount
Sub CountPatientVisit(LastPatientRow, LastVisitColumn)
Application.ScreenUpdating = False
MorePatients = True
LastPatientRow = 0
RowIndex = 3
If Worksheets("Summary").Cells(RowIndex, 2) <> "" Then
LastPatientRow = RowIndex
RowIndex = RowIndex + 1
MorePatients = False
MoreVisits = True
LastVisitColumn = 0
columnindex = 3
If Worksheets("Visit Patterns").Cells(2, columnindex) <> "" Then
LastVisitColumn = columnindex
columnindex = columnindex + 1
MoreVisits = False