Code required please

[Guest]

Hi. Some help with this would be appreciated......

I have a worksheet with a list of numbers in column "A".
I would like a command button on the sheet that when pressed acivates an
input box requseting the user to enter a number. When the number is entered
the code would search column "A" and if a match is found then display this
fact to the user. If no match is found then add the new number to the list
and also advise the user that this has in fact been done.

thanks in advance

 

[Guest]

How about something like:

Option Explicit

Sub FindMyNum()

Dim rng As Range
Dim NUM As Long
Dim NextCell As Range

Set NextCell = Range("A65536").End(xlUp)(2)
NUM = InputBox("ENTER A NUMBER")

Set rng = Range("A1", NextCell)
With rng
On Error Resume Next
If IsError(.Find(WHAT:=NUM, AFTER:=NextCell).Select) Then
MsgBox "Could not find your number. Will add it to the list.",
vbOKOnly, ""
NextCell = NUM
Else
MsgBox "Your number is located at: " & Selection.Address
End If
End With
End Sub

 

[Guest]

How about something like:

Option Explicit

Sub FindMyNum()

Dim rng As Range
Dim NUM As Long
Dim NextCell As Range

Set NextCell = Range("A65536").End(xlUp)(2)
NUM = InputBox("ENTER A NUMBER")

Set rng = Range("A1", NextCell)
With rng
On Error Resume Next
If IsError(.Find(WHAT:=NUM, AFTER:=NextCell).Select) Then
MsgBox "Could not find your number. Will add it to the list.",
vbOKOnly, ""
NextCell = NUM
Else
MsgBox "Your number is located at: " & Selection.Address
End If
End With
End Sub