variable/Input box problem

[john.9.williams]

Hi i have a program that takes information from inputboxes. The user
enters the info in and it adds it to a list within the spreadsheet.
The info entered can be text or numerical. When the user enters the
info the program looks to see if they have already entered that info
onto the list.

If the user adds text it checks the list and returns a message if they
have already entered that text. But when i enter a number that i have
already entered then it still allow me to input it onto the list. I
have tried declaring the variable assigned to the input box as an
integer but get an integer, it then checks the numbers entered ok, but
throws out an error if i eneter a text value. here the bit of code i
am using. any help greatly recieved.

Sub ADDMAN()
Dim clear
Dim JOBCHECK

'this bit makes sure the users enters something'
Do While clear = ""
clear = InputBox("Please enter A1024/D-Pole Ref")
Loop

'this bit checks the info has not already been entered'
JOBCHECK = 0
Sheets("WORKLIST").Select
Range("A1").Select
Do While Selection.Value <> ""
ActiveCell.Offset(1, 0).Select
If ActiveCell = clear Then JOBCHECK = 1
Loop

If JOBCHECK = 1 Then MsgBox Title:="POLETRACKER", PROMPT:="SORRY THIS
JOB ALREADY EXISTS ON YOUR CURRENT WORKSTACK!"
If JOBCHECK = 1 Then Sheets("MENU").Select
If JOBCHECK = 1 Then exit sub

'THIS BIT ENTERS THE VALUE TO THE LIST'
Sheets("WORKLIST").Select
Range("A1").Select
Do While Selection.Value <> ""
ActiveCell.Offset(1, 0).Select
Loop

ActiveCell = clear


END SUB

 

[Tom Ogilvy]

If lcase(Trim(ActiveCell.Text)) = lcase(Trim(cStr(clear))) Then JOBCHECK = 1

 

[Bernie Deitrick]

John,

Change

If ActiveCell = clear Then JOBCHECK = 1

to

If ActiveCell = CStr(clear) Then JOBCHECK = 1

But, there isn't any need for all that looping. See the example below,
which also has better entry checking and loops back to the beginning if the
entry was already found.

HTH,
Bernie
MS Excel MVP

Sub AddMan()
Dim strClear As String

GetEntry:
strClear = ""

'this bit makes sure the users enters something'
Do While strClear = ""
strClear = Application.InputBox("Please enter A1024/D-Pole Ref")
If strClear = "False" Then Exit Sub ' User hit cancel
Loop

'this bit checks the info has not already been entered'
If Application.CountIf(Sheets("WORKLIST").Range("A:A"), _
strClear) = 0 Then
Range("A65536").End(xlUp)(2).Value = strClear
Else
MsgBox "Sorry, job already in the workstack.", _
vbOKOnly, "PoleTracker"
Sheets("MENU").Select
GoTo GetEntry
End If

End Sub