Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need to check for a number in a column before continuing on
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I need to check for a number in a column before continuing on
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 "Don M." wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check Cell Content before continuing... | Excel Programming | |||
How can I check for a duplicate number in a column in Excel? | Excel Discussion (Misc queries) | |||
Continuing equation a set number of times | Excel Discussion (Misc queries) | |||
Continuing Number Sequence | Excel Discussion (Misc queries) | |||
Continuing Difficulties W/ Sum For Variable-length Column | Excel Programming |