I need to search from a data in excel using a macro
If for example above image is a default list and if i gave a input using a input box.
Case:1 If input as "a1,a2,b1,c4" it should reflect "All data is correct"
Case:2 If input is "a1,a2,b5,c5,c4" It should reflect " Error: b5 & c5 are not available" & should run the loop again till it becomes a perfect match (like in case1)
1 Answer
Try the code below. It asks for the titles input, then check if there are correct. If some are not correct, a message box shows the incorrect tiles. Then, an input box is shown again to enter titles. If the titles are correct, then a message box is displayed, nd the loop is exited.
Sub FindTitles()
Dim blnCorrect, blnFound As Boolean
Dim myarray
Dim myinput, notitle As String
Dim lRow, mybound As Long
'Find the last non-blank cell in column A(1)
lRow = Cells(Rows.Count, 1).End(xlUp).Row
'Loop while entered values are incorrect
Do While blnCorrect = False
'Get the titles
myinput = InputBox("Enter titles", "Titles")
myarray = Split(myinput, ",")
mybound = UBound(myarray) - LBound(myarray) + 1
notitle = ""
'Loop through the entered titles
For x = 0 To mybound - 1
'Loop through the titles on the worksheet
For Each mycells In Range("A2:A" & lRow)
'If title is found, exit the loop
If mycells.Value = myarray(x) Then
blnFound = True
Exit For
Else
blnFound = False
End If
Next
If Not blnFound Then
'Append the title not found
notitle = notitle & myarray(x) & " & "
End If
Next
If notitle = "" Then
MsgBox Prompt:="All titles are available", Title:="Title(s) found"
'Set blnCorrect to True to exit the loop
blnCorrect = True
Else
notitle = Left(notitle, (Len(notitle) - 3))
MsgBox Prompt:= " Error: " & notitle & " are not available", Title:="Title(s) not found"
End If
Loop
End Sub
