I need to check for a number in a column before continuing on

[Don M.]

I have written a macro to look for a number in a column then continue with
the macro. The problem is that these numbers are no consecutive and some
numbers may be missing. The number to search for comes from a TextBox called
txtRegNum in a userform called frmScores after a button called cmdAddScores
is clicked.

I need some code that will check if the number exists before doing the
search for the number. If the number does not exist in the column then I need
a msgbox to pop up and prompt for a valid number.

Sample Data:

# Name Gender Grade Teacher
1 Don Boy 2 Boucher
3 Kristy Girl 3 Chalk
4 Sophia Girl 4 Dillingham
6 Addison Girl 3 Devore
9 Dale Boy 3 Chalk

If I search for 2 in column A then I just get a fatal error. I need the
macro to prompt for a valid search number and keep going.

Don

Current Macro:

Private Sub cmdAddScores_Click()
Dim ws As Worksheet
Set ws = Worksheets("Registry")

'check for a registration number
If Trim(Me.txtRegNum.Value) = "" Then
Me.txtRegNum.SetFocus
MsgBox "Please enter a rider number."
Exit Sub
End If

'I need to verify that txtRegNum exists in
'column A before going any further

'If it doesn't, then prompt the user for a
'valid number similar to message above

'find registration number in database
Columns("A:A").Select
Selection.Find(What:=txtRegNum).Activate
ActiveCell.Offset(0, 6).Activate

'copy the data to the database
ActiveCell.Offset(0, 0).Value = Me.txtQuizScore.Value
ActiveCell.Offset(0, 1).Value = Me.txtCourse1.Value
ActiveCell.Offset(0, 2).Value = Me.txtCourse2.Value
ActiveCell.Offset(0, 3).Value = Me.txtCourse3.Value
ActiveCell.Offset(0, 4).Value = Me.txtCourse4.Value

'clear the data
Me.txtRegNum.Value = ""
Me.txtQuizScore.Value = ""
Me.txtCourse1.Value = ""
Me.txtCourse2.Value = ""
Me.txtCourse3.Value = ""
Me.txtCourse4.Value = ""
Me.txtRegNum.SetFocus

End Sub

 

[Don M.]

I found it.

'find registration number in registry
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Check = False: Row = 2 ' Initialize variables.
Do While Row <= lRow ' Inner loop.
RegNum = txtRegNum.Value + 0
Cell = Cells(Row, 1)
If Cells(Row, 1) = RegNum Then
Check = True ' Set value of flag to False.
Exit Do ' Exit inner loop.
End If
Row = Row + 1 ' Increment Row.
Loop

If Check = False Then
Me.txtRegNum.SetFocus
MsgBox "Please enter a valid rider number."
Exit Sub
End If