Is the name found in both lists?

[anish201]

here are 2 lists of employees on the same work sheet in Col A and Col B. They have different lenghts. I am trying to write a sub where the program prompts the user for the name of an employee. Then the program checks of the name of the employee entered is in both lists. If yes then it gives me a message, "yeah, its in both lists", if not then it gives me a message "no, it is not present in both lists" here is my code so far



Sub Main()
Dim Ncust99 As Integer, Ncust2000 As Integer, i As Integer, j As Integer, _
Cust99Name() As String, Cust2000Name() As String, Entername As String
With Range("a1")
Ncust99 = Range(.Offset(1, 0).End(xlDown), Cells(2, 1)).Rows.Count
ReDim Cust99Name(Ncust99)
Ncust2000 = Range(.Offset(1, 1).End(xlDown), Cells(2, 2)).Rows.Count
ReDim Cust2000Name(Ncust2000)
For i = 1 To Ncust99
Cust99Name(i) = .Offset(i, 0)
Next i
End With
With Range("b1")
For j = 1 To Ncust2000
Cust2000Name(j) = .Offset(j, 0)
Next j
End With
Entername = InputBox("Enter a customer name")
With Range("a1")
For i = 1 To Ncust99
If Entername = Cust99Name(i) Then
MsgBox " Yes name is present"
Else
MsgBox " No its not present"
End If
Next i
End With
End Sub

[Jacob Hilderbrand]

Try this macro.


Option Explicit

Sub Macro1()
Dim Prompt As String
Dim Title As String
Dim SearchString As String
Prompt = "Enter a customer name"
Title = "Customer Name Input"
SearchString = InputBox(Prompt, Title)
If SearchString <> "" Then
If Application.WorksheetFunction.CountIf(Range("A:A"), SearchString) > 0 And _
Application.WorksheetFunction.CountIf(Range("B:B"), SearchString) > 0 Then
MsgBox "Name is in both lists"
Else
MsgBox "Name is not in both lists"
End If
End If
End Sub

[anish201]

Thanks buddy, that worked. I really appreciate the help

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care