random search with multiple conditions

[Blanchetdb]

Hi

I am looking to do a random search on a sheet for auditing purposes. One issue is that the range of the search is not constant as new entries are done to the sheet weekly. I have two conditions: 1) the record must not have already been audited, 2) the result cannot return a blank

Level	Process#	Candidate	Branch		Audited
2	21-ICS-OE-HRSD-456	John Smith	Operations		Yes
6	20-ICS-OI-HRSD-243	Jane Doe	Operations		No
8	22-ICS-OE-HRSD-589	Steve Parker	Operations		No
7	23-ICS-OE-HRSD-247	Ben Warren	Operations		Yes

my present sheet has over 500 entries, but this is a small sample.

So, I need to press a button and a process# (from column B) appears in cell H4 on the same sheet but it cannot be a process that has already been audited (Yes)

Can someone help me with that?

 

[Joe4]

I hadn't looked really closely at the entire code he first posted. It does not do what you asked. It finds a code that you enter, which is not what you are asking for at all (finding a random "No" value from your list).

Assuming that you have headers in row 1, your data starts on row 2, and your "Audited" column is column F, try this code:

Sub PickRandomRecord()
    
    Dim lr As Long
    Dim r As Long
    Dim rng As Range
    Dim numNo As Long
    Dim rNum As Long
    Dim ct As Long
    
'   Find last row with data in column F
    lr = Cells(Rows.Count, "F").End(xlUp).Row
    
'   Build range to check in column F
    Set rng = Range("F2:F" & lr)
    
'   Count the number of "No"s in column F
    numNo = Application.WorksheetFunction.CountIf(rng, "No")
    
'   Build random number
    rNum = Int(1 + rnd * numNo)
    
'   Loop through "No" records in column F until you hit the random number
    For r = 2 To lr
'       Check to see if value is a "No"
        If Cells(r, "F") = "No" Then
'           Add 1 to counter
            ct = ct + 1
'           See if counter matches random number
            If ct = rNum Then
'               Write value to cell H4
                Range("H4").Value = Range("B" & r).Value
'               Exit for loop
                Exit For
            End If
        End If
    Next r

End Sub

 

[Blanchetdb]

I hadn't looked really closely at the entire code he first posted. It does not do what you asked. It finds a code that you enter, which is not what you are asking for at all (finding a random "No" value from your list).

Assuming that you have headers in row 1, your data starts on row 2, and your "Audited" column is column F, try this code:

Sub PickRandomRecord()
 
    Dim lr As Long
    Dim r As Long
    Dim rng As Range
    Dim numNo As Long
    Dim rNum As Long
    Dim ct As Long
 
'   Find last row with data in column F
    lr = Cells(Rows.Count, "F").End(xlUp).Row
 
'   Build range to check in column F
    Set rng = Range("F2:F" & lr)
 
'   Count the number of "No"s in column F
    numNo = Application.WorksheetFunction.CountIf(rng, "No")
 
'   Build random number
    rNum = Int(1 + rnd * numNo)
 
'   Loop through "No" records in column F until you hit the random number
    For r = 2 To lr
'       Check to see if value is a "No"
        If Cells(r, "F") = "No" Then
'           Add 1 to counter
            ct = ct + 1
'           See if counter matches random number
            If ct = rNum Then
'               Write value to cell H4
                Range("H4").Value = Range("B" & r).Value
'               Exit for loop
                Exit For
            End If
        End If
    Next r

End Sub

thank you for providing this....

So I copied the macro (made a few changes) but nothing happens.

The column that contains either Yes or NO is column AN (the table above was solely an example) which I did make a modification in the macro.

The random search needs to produce a process number from Column F and the conditions are:
1. Not previously audited (so column AN would show "NO" in that cell on that row) ... example:

Column F.....................................................................................................................................................................................................................................................................................................................................Column AN

21-ICA-NCR-OE-VAR-HRSD-275	NO
23-ICA-ONT-OE-VAR-PPB-786	NO
22-ICA-WEST-IO-VAR-OPS-987	YES

The random search can return either 21-ICA-NCR-OE-VAR-HRSD-275 or 23-ICA-ONT-OE-VAR-PPB-786 as neither record has been audited

2. The search cannot return a blank

the sheet has continuous entry but once a row has information in it, column F will always have information in the corresponding cell (either a process# or TBD)

I hope this clarifies things.

thank you for your help

 

[Joe4]

This is a very important lesson. Unless you are REALLY comfortable modifying someone else's, you will want to be as descriptive/detailed as possible, so we know where everything is located. You don't want to oversimplify things. There is no need to do so for our sake. Otherwise, you may get a reply that answers the question you actually asked, but not your real problem. Remember, all that we know about the details is what you decide to share us with us. So help us to help you!

In many cases, it is very helpful to post a sample where we can clearly see exactly what you are working with, and where all that data is actually located (row and column locations). MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in. Just be sure to remove any sensitive data first.

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you are unable to post a clearer picture of your data that shows where everything is, please answer the following questions:
1. On that row does your actual data start?
2. Do you still want the result in cell H4, or some other cell?

 

[Blanchetdb]

This is a very important lesson. Unless you are REALLY comfortable modifying someone else's, you will want to be as descriptive/detailed as possible, so we know where everything is located. You don't want to oversimplify things. There is no need to do so for our sake. Otherwise, you may get a reply that answers the question you actually asked, but not your real problem. Remember, all that we know about the details is what you decide to share us with us. So help us to help you!

In many cases, it is very helpful to post a sample where we can clearly see exactly what you are working with, and where all that data is actually located (row and column locations). MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in. Just be sure to remove any sensitive data first.

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you are unable to post a clearer picture of your data that shows where everything is, please answer the following questions:
1. On that row does your actual data start?
2. Do you still want the result in cell H4, or some other cell?

my apologies for making this more complicated than what it needs to be.

the data actually starts on row 10 and the result can appear in cell H4

 

[Joe4]

OK, a few small edits is all that is really needed.

Sub PickRandomRecord()
    
    Dim lr As Long
    Dim r As Long
    Dim rng As Range
    Dim numNo As Long
    Dim rNum As Long
    Dim ct As Long
    
'   Find last row with data in column AN
    lr = Cells(Rows.Count, "AN").End(xlUp).Row
    
'   Build range to check in column AN
    Set rng = Range("AN2:AN" & lr)
    
'   Count the number of "No"s in column AN
    numNo = Application.WorksheetFunction.CountIf(rng, "No")
    
'   Build random number
    rNum = Int(1 + Rnd * numNo)
    
'   Loop through "No" records in column AN until you hit the random number
    For r = 10 To lr
'       Check to see if value is a "No"
        If Cells(r, "AN") = "No" Then
'           Add 1 to counter
            ct = ct + 1
'           See if counter matches random number
            If ct = rNum Then
'               Write value to cell H4
                Range("H4").Value = Range("F" & r).Value
'               Exit for loop
                Exit For
            End If
        End If
    Next r

End Sub

 

[Blanchetdb]

OK, a few small edits is all that is really needed.

Sub PickRandomRecord()
   
    Dim lr As Long
    Dim r As Long
    Dim rng As Range
    Dim numNo As Long
    Dim rNum As Long
    Dim ct As Long
   
'   Find last row with data in column AN
    lr = Cells(Rows.Count, "AN").End(xlUp).Row
   
'   Build range to check in column AN
    Set rng = Range("AN2:AN" & lr)
   
'   Count the number of "No"s in column AN
    numNo = Application.WorksheetFunction.CountIf(rng, "No")
   
'   Build random number
    rNum = Int(1 + Rnd * numNo)
   
'   Loop through "No" records in column AN until you hit the random number
    For r = 10 To lr
'       Check to see if value is a "No"
        If Cells(r, "AN") = "No" Then
'           Add 1 to counter
            ct = ct + 1
'           See if counter matches random number
            If ct = rNum Then
'               Write value to cell H4
                Range("H4").Value = Range("F" & r).Value
'               Exit for loop
                Exit For
            End If
        End If
    Next r

End Sub

I did a copy/paste and nothing happened

I "click" the command button but nothing appears in cell H4 or anywhere else

I've made sure that the macro is assigned to the correct command button.

 

[Joe4]

Case matters!

I did not notice that you changed your data. In your original post, your example showed "Yes" and "No".
But it appears that this was not an accurate depiction of what you actually have, which looks like it is really "YES" and "NO".

So you will need to reflect that in the code, i.e.

Sub PickRandomRecord()
    
    Dim lr As Long
    Dim r As Long
    Dim rng As Range
    Dim numNo As Long
    Dim rNum As Long
    Dim ct As Long
    
'   Find last row with data in column AN
    lr = Cells(Rows.Count, "AN").End(xlUp).Row
    
'   Build range to check in column AN
    Set rng = Range("AN2:AN" & lr)
    
'   Count the number of "No"s in column AN
    numNo = Application.WorksheetFunction.CountIf(rng, "NO")
    
'   Build random number
    rNum = Int(1 + Rnd * numNo)
    
'   Loop through "No" records in column AN until you hit the random number
    For r = 10 To lr
'       Check to see if value is a "NO"
        If Cells(r, "AN") = "NO" Then
'           Add 1 to counter
            ct = ct + 1
'           See if counter matches random number
            If ct = rNum Then
'               Write value to cell H4
                Range("H4").Value = Range("F" & r).Value
'               Exit for loop
                Exit For
            End If
        End If
    Next r

End Sub

 

[Blanchetdb]

Case matters!

I did not notice that you changed your data. In your original post, your example showed "Yes" and "No".
But it appears that this was not an accurate depiction of what you actually have, which looks like it is really "YES" and "NO".

So you will need to reflect that in the code, i.e.

Sub PickRandomRecord()
 
    Dim lr As Long
    Dim r As Long
    Dim rng As Range
    Dim numNo As Long
    Dim rNum As Long
    Dim ct As Long
 
'   Find last row with data in column AN
    lr = Cells(Rows.Count, "AN").End(xlUp).Row
 
'   Build range to check in column AN
    Set rng = Range("AN2:AN" & lr)
 
'   Count the number of "No"s in column AN
    numNo = Application.WorksheetFunction.CountIf(rng, "NO")
 
'   Build random number
    rNum = Int(1 + Rnd * numNo)
 
'   Loop through "No" records in column AN until you hit the random number
    For r = 10 To lr
'       Check to see if value is a "NO"
        If Cells(r, "AN") = "NO" Then
'           Add 1 to counter
            ct = ct + 1
'           See if counter matches random number
            If ct = rNum Then
'               Write value to cell H4
                Range("H4").Value = Range("F" & r).Value
'               Exit for loop
                Exit For
            End If
        End If
    Next r

End Sub

Case matters!

I did not notice that you changed your data. In your original post, your example showed "Yes" and "No".
But it appears that this was not an accurate depiction of what you actually have, which looks like it is really "YES" and "NO".

So you will need to reflect that in the code, i.e.

Sub PickRandomRecord()
  
    Dim lr As Long
    Dim r As Long
    Dim rng As Range
    Dim numNo As Long
    Dim rNum As Long
    Dim ct As Long
  
'   Find last row with data in column AN
    lr = Cells(Rows.Count, "AN").End(xlUp).Row
  
'   Build range to check in column AN
    Set rng = Range("AN2:AN" & lr)
  
'   Count the number of "No"s in column AN
    numNo = Application.WorksheetFunction.CountIf(rng, "NO")
  
'   Build random number
    rNum = Int(1 + Rnd * numNo)
  
'   Loop through "No" records in column AN until you hit the random number
    For r = 10 To lr
'       Check to see if value is a "NO"
        If Cells(r, "AN") = "NO" Then
'           Add 1 to counter
            ct = ct + 1
'           See if counter matches random number
            If ct = rNum Then
'               Write value to cell H4
                Range("H4").Value = Range("F" & r).Value
'               Exit for loop
                Exit For
            End If
        End If
    Next r

End Sub

I had made that change in the code from No to NO …. But still got nothing.

I even opened a new workbook created the same data sheet, added the code but nothing

Case matters!

I did not notice that you changed your data. In your original post, your example showed "Yes" and "No".
But it appears that this was not an accurate depiction of what you actually have, which looks like it is really "YES" and "NO".

So you will need to reflect that in the code, i.e.

Sub PickRandomRecord()
   
    Dim lr As Long
    Dim r As Long
    Dim rng As Range
    Dim numNo As Long
    Dim rNum As Long
    Dim ct As Long
   
'   Find last row with data in column AN
    lr = Cells(Rows.Count, "AN").End(xlUp).Row
   
'   Build range to check in column AN
    Set rng = Range("AN2:AN" & lr)
   
'   Count the number of "No"s in column AN
    numNo = Application.WorksheetFunction.CountIf(rng, "NO")
   
'   Build random number
    rNum = Int(1 + Rnd * numNo)
   
'   Loop through "No" records in column AN until you hit the random number
    For r = 10 To lr
'       Check to see if value is a "NO"
        If Cells(r, "AN") = "NO" Then
'           Add 1 to counter
            ct = ct + 1
'           See if counter matches random number
            If ct = rNum Then
'               Write value to cell H4
                Range("H4").Value = Range("F" & r).Value
'               Exit for loop
                Exit For
            End If
        End If
    Next r

End Sub

It worked ..... put it in a module instead of the sheet and it worked ... thank you

 

[Joe4]

I had made that change in the code from No to NO …. But still got nothing.

I even opened a new workbook created the same data sheet, added the code but nothing

It worked ..... put it in a module instead of the sheet and it worked ... thank you

Sorry, didn't realize that you were putting it in the wrong place.
That was going to be my next line of questioning, where you are putting the code.

Generally, the only code you put in Sheet and Workbook modules are automated event procedures (VBA code that runs automatically upon some event happening, like changing a cell, opening a workbook, etc). Most everything else you put in a "General" Module that you insert.

Anyway, glad you got it working the way you need now.