VBA code for Multiple inputs search comparison
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
