find multiple criteria in 2 columns

[orsm6]

Hi all - i have tried to adapt the following code but it seems to not be working, hoping someone could provide a solution.

using a macro to call userform1
command button in user form is meant to copy the value in text box to LOG sheet (this works fine)
but then i wanted to call another macro to check Columns A and B.... Col A for the number that was in the text box, and Col B for the number 1.

if BOTH conditions exist give msgbox and exit sub, if not, continue.

the problem i have is that regardless of conditions the msgbox appears. and i am assuming it is calling the Call Submit macro.... so i don't think my check is being done right...

Sub cardcheck()
 
Dim rowCount As Long, i As Long
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("LOG")
Dim FindString As String
                
            FindString = Worksheets("LOG").Cells(1, 7).Value
            rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

            Application.ScreenUpdating = False
                'i refers to row number
                For i = 1 To rowCount
                    If ws.Range("A" & i) = FindString And ws.Range("b" & i) = 1 Then
                        MsgBox "This card number already used in survey, response not submitted."
                        Worksheets("Survey").Activate
                        Exit Sub
                    Else
                        Call submit
                    End If
                Next i
End Sub

TIA

 

[orsm6]

post cleared, thought i had the solution, but may not have

 

[Alex Blakenburg]

You might need to clarify what you are trying to achieve.
Your macro is starting in row 1 and will "Call submit" multiple times until it either reaches a row meeting the condition (at which point it will show the message box) or reaches rowCount.
Are you intending to run Call submit multiple times ?

 

[orsm6]

You might need to clarify what you are trying to achieve.
Your macro is starting in row 1 and will "Call submit" multiple times until it either reaches a row meeting the condition (at which point it will show the message box) or reaches rowCount.
Are you intending to run Call submit multiple times ?

Hi Alex - yeah you're right... it does call all the time (but i do not want it to), i initially had the submit macro there then realised i wanted some other code before that.

my workbook contains a sheet called LOG. it holds a list of all of the card numbers assigned to people on site.
the document is a survey - they have to enter their card number to allow them to submit their survey response.

my end goal is this:

- click a yes but, launches userform1.
- on userform1 user enters their card number and clicks submit.
- this then checks on the LOG sheet if the card number has already been submitted previously.
..... this is where i need help: to check the log sheet for the card value in column A and the number 1 in column B

- if criteria is met, msg box and exit sub.

- if criteria not met, allow the entry by finding the card number on LOG sheet in column A and puts a 1 next to the card number in column B

 

[orsm6]

You might need to clarify what you are trying to achieve.
Your macro is starting in row 1 and will "Call submit" multiple times until it either reaches a row meeting the condition (at which point it will show the message box) or reaches rowCount.
Are you intending to run Call submit multiple times ?

I used this Ron deBruin code that works for finding 1 value... but do not know if or how to expand on it to add a second value as an AND function.

Sub cardcheck()

    Dim FindString As String
    Dim Rng As Range
    FindString = Worksheets("LOG").Cells(1, 7).Value
 
   If Trim(FindString) <> "" Then
        With Sheets("LOG").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                           
            If Not Rng Is Nothing Then
                userform1.Hide
               MsgBox "This card number has previously been used to access this survey, thank you."
            Else
               Call submitYES

           End If
        End With
    End If

End Sub

 

[Alex Blakenburg]

Give this a try:-

Sub Test_Find()

    Dim rowCount As Long, i As Long
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("LOG")
    Dim FindString As String
                
        FindString = Worksheets("LOG").Cells(1, 7).Value
        rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        
        Dim foundCell As Range
        Set foundCell = ws.Range("A1:A" & rowCount).Find(What:=FindString, After:=ws.Range("A1"), _
                            LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

        Application.ScreenUpdating = False

        If Not foundCell Is Nothing Then
            If ws.Range("B" & foundCell.Row) = 1 Then
                MsgBox "This card number already used in survey, response not submitted."
                Worksheets("Survey").Activate
                Exit Sub
            Else
                Call submit
            End If
        Else
            Call submit
        End If

End Sub

 

[orsm6]

Give this a try:-

Sub Test_Find()

    Dim rowCount As Long, i As Long
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("LOG")
    Dim FindString As String
               
        FindString = Worksheets("LOG").Cells(1, 7).Value
        rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
       
        Dim foundCell As Range
        Set foundCell = ws.Range("A1:A" & rowCount).Find(What:=FindString, After:=ws.Range("A1"), _
                            LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

        Application.ScreenUpdating = False

        If Not foundCell Is Nothing Then
            If ws.Range("B" & foundCell.Row) = 1 Then
                MsgBox "This card number already used in survey, response not submitted."
                Worksheets("Survey").Activate
                Exit Sub
            Else
                Call submit
            End If
        Else
            Call submit
        End If

End Sub

that's impressive.

does just as required thank you very much

 

[Alex Blakenburg]

Thanks for letting me know, glad I could help.
PS: You should be able to reuse the logic when the survey is entered to see if you have an entry there with B = 0 or blank and needing to be updated to 1