briannnn
03-27-2020, 07:33 AM
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
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