Finding a value - error correction

[Logistix]

Hi there,

I have a userform that currently locates a particular cell (value) when I push the find button. Im a little stuck, bacause when I input a number not in the column (or blank for that matter).

I get the error "Select method of Range Class failed".

Does anyone know how to do a check to see if the value exists in the column, and if not prompt the user that the number is invalid and to re-enter.

Here's the code I have so far:

Private Sub FindButton_Click()
    
    Worksheets("Records").Range("A:A").Find(TransactionNoBox.Value).Select   ' This finds my value
    
    TransactionNumberForm.Hide
    DeleteConfirmationForm.Show
    
End Sub

I think I need a while loop here but am not too familiar with them yet. Thanks in advance.

 

[Logistix]

You're then man!! Thanks it works great!

 

[Logistix]

Hey one more thing....

If I leave the TransactionNoBox empty, and press find, it still prompts me with the msgbox delete. Do you know what has happened here

 

[jindon]

Yup!
change..

Private Sub FindButton_Click()
Dim r As Range
   If TransactionNoBox="" Then Exit Sub
   Set r = Sheets("Records").Columns("a").Find(TransactionNoBox.Text,,,xlWhole)
   If Not r Is Nothing Then
      If vbYes = MsgBox("Delete?",vbYesNo,"Confirm") Then
         r.EntireRow.Delete
      End If
   Else
      MsgBox "Not Found"
   End If
End Sub

 

[Logistix]

Thanks!