Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 51
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 51
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Check Cell Content before continuing... Chris T-M Excel Programming 5 November 1st 07 04:39 AM
How can I check for a duplicate number in a column in Excel? kkinner Excel Discussion (Misc queries) 2 August 24th 06 03:44 PM
Continuing equation a set number of times Tim Excel Discussion (Misc queries) 1 August 3rd 05 04:55 PM
Continuing Number Sequence Jen Excel Discussion (Misc queries) 2 January 12th 05 08:28 PM
Continuing Difficulties W/ Sum For Variable-length Column Chuckles123[_3_] Excel Programming 3 October 4th 04 12:01 AM


All times are GMT +1. The time now is 04:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"