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?

 

[ranman256]

paste code into a module. run: FindProc
(you can put this macro btn on the Quick Access tool bar)
this assumes your last data column is F

Sub FindProc()
Dim lRows As Long
Dim vProcNum

On Error GoTo errFind
vProcNum = InputBox("Enter Proc#", "Find Proc#")
If vProcNum = "" Then Exit Sub

    Range("A1").Select
    ActiveSheet.UsedRange.Select
    lRows = ActiveSheet.UsedRange.Rows.Count
 
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$" & lRows).AutoFilter Field:=6, Criteria1:="No"
    Range("A1:F" & lRows).Select

    Selection.Find(What:=vProcNum, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
 
    ActiveCell.Select
    MsgBox "Found"
Exit Sub
errFind:
Selection.AutoFilter
Range("A1").Select
MsgBox "[" & vProcNum & "] not found"
End Sub

 

[Blanchetdb]

paste code into a module. run: FindProc
(you can put this macro btn on the Quick Access tool bar)
this assumes your last data column is F

Sub FindProc()
Dim lRows As Long
Dim vProcNum

On Error GoTo errFind
vProcNum = InputBox("Enter Proc#", "Find Proc#")
If vProcNum = "" Then Exit Sub

    Range("A1").Select
    ActiveSheet.UsedRange.Select
    lRows = ActiveSheet.UsedRange.Rows.Count
 
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$" & lRows).AutoFilter Field:=6, Criteria1:="No"
    Range("A1:F" & lRows).Select

    Selection.Find(What:=vProcNum, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
 
    ActiveCell.Select
    MsgBox "Found"
Exit Sub
errFind:
Selection.AutoFilter
Range("A1").Select
MsgBox "[" & vProcNum & "] not found"
End Sub

thank you for the response...

I added the macro but got the following error message:

Also, the last column is AN which I made the required changes in the macro you gave me but not sure how this will populate a process# from column B into cell H4

 

[ranman256]

try changing it to: xlValues

 

[Joe4]

thank you for the response...

I added the macro but got the following error message:

View attachment 98776
Also, the last column is AN which I made the required changes in the macro you gave me but not sure how this will populate a process# from column B into cell H4

I believe that "xlFormulas2" only works in newer versions of Excel.
Older versions would have to use "xlFormulas", for the equivalent.

 

[Blanchetdb]

try changing it to: xlValues

I still get the "Can't find project or library" message but nothing is highlighted.

I tried the xlValues and xlFormulas ......

 

[ranman256]

hm
xlValues = -4163
so replace xValues with that number.

Selection.Find(What:=vProcNum, After:=ActiveCell, LookIn:=-4163 , _...

 

[Blanchetdb]

hm
xlValues = -4163
so replace xValues with that number.

Selection.Find(What:=vProcNum, After:=ActiveCell, LookIn:=-4163 , _...

now I get the following:

 

[Joe4]

now I get the following:

View attachment 98809

You left out the error handling code at the bottom of the code that Ranman256 gave you in his first reply.
There is more code between the "Exit Sub" and "End Sub" lines.

 

[Blanchetdb]

now I get the following:

View attachment 98809

okay I get the following:

I may have not explained myself clearly ....

I require the search to extract a process number that already exists in column F. The purpose of the search is so that it gives me a process number that will need to be audited. The audit process must be at random as I cannot go in the list and pick one visually (for example, I can't just go to the next process that has "No" in column AN)

I hope this clarifies what I need, and I apologize if I wasn't clear.