VBA Do While Loop Question

[briannnn]

I am a newbie to VBA. The topic I am facing now is looping.
There is an excel sheet with years from 1991 to 2019 and different numbers of names under each year.
The requirements are to create two inputboxes, one asking the users to input a name and the other one asking for a year. If there is a match in the selected year column, it will display a message to let the user know there is a match in which row, then the sub ends. If there is no match after searching the whole column, it will also display a message to indicate there is no match.

I have tried to finished some steps but have no idea on the others. Please help and many thanks!

This is how the data looks like:
26226
This is the VBA code:

Sub DoLoop1()
Dim searchCol As Integer
Dim nCols As Integer
Dim rowCount As Integer
Dim foundMatch As Boolean
Dim reqName As String

Sheet1.Activate
With Range("A1")
' use nCols variable here to identify the
' number of columns to be searched
nCols = Cells(1, Columns.Count).End(xlToLeft).Column
End With

' use reqName variable to capture the name to search for
' using an input box function
reqName = LCase(InputBox("Enter the name"))

' use input box function to collect year to search, year - 1990 to
' get searchCol, which is to determine the column number to search
' for customer name, and also to check if year is a valid year
' You may want to use a Do loop here for checking user input of the year
searchCol = InputBox("Enter the year")
Do While searchCol <= "1990" Or searchCol > "2019"
searchCol = InputBox("Enter the year again")
Loop

rowCount = 2
foundMatch = False

' use Do While ... Loop, to repace the for... next loop,
' and If/Then/Else to search the customer name
' until it hits an empty cell in the search column.
' if there is a match in the search row, (1) display a msgbox
' with appropriate title and icon to show the customer name
' and record number (not row number), and (2) exit the do loop;
'
' if there is no match in the search row, manually move the
' search to next row.
' note that if there is no name at all in the selected
' column, the body of the loop will never execute
Do While Cells(rowCount, 1).Value <> ""
Loop

If Not foundMatch Then
MsgBox "No match for " & reqName & " was found.", vbInformation, _
"DoLoop1 - No Match"
End If

[briannnn]

Anyone can help ? THX

[jolivanes]

Maybe attach a workbook with the data so people don't have to create it for you.

Re: "it will display a message to let the user know there is a match in which row"
Don't need a macro for that. That should always be Row1

Row1 does just not only have the year in it by the looks of it.

[Paul_Hossler]

Option Explicit


Sub test()
Dim iYear As Long
Dim sName As String
Dim iRow As Long, iCol As Long

iYear = Application.InputBox("Enter Year", "Year", 0)
If iYear = 0 Then Exit Sub


iCol = 0
On Error Resume Next
iCol = Rows("1:1").Find(What:=iYear, After:=Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
On Error GoTo 0

If iCol = 0 Then
Call MsgBox("No year " & iYear, vbCritical + vbOKOnly, "Errror")
Exit Sub
End If


sName = Application.InputBox("Enter Name", "Name", vbNullString)
If Len(sName) = 0 Then Exit Sub


iRow = 0
On Error Resume Next
iRow = Application.WorksheetFunction.Match(sName, Columns(iCol), 0)
On Error GoTo 0

If iRow = 0 Then
Call MsgBox("No name = " & sName & " in " & iYear & " found", vbCritical + vbOKOnly, "Errror")

Else
Call MsgBox(sName & " found in in Column " & iCol & ", row " & iRow, vbInformation + vbOKOnly, "Yea!!")
End If


End Sub