Finding a value - error correction

Logistix

Board Regular
Joined
Aug 23, 2006
Messages
77
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:

Code:
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80
I do a COUNTIF before I do a MATCH or FIND. If COUNTIF>0 then I know that MATCH or FIND will not Abend.


If Application.WorksheetFunction.CountIf(Worksheets("Records").Range("A:A"), TransactionNoBox.Value) > 0 Then
Worksheets("Records").Range("A:A").Find(Worksheets("Records").Range("A:A").Find(TransactionNoBox.Value).Select).Select
End If
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
It is always good to have object(range) variable to see if the cell is found or not.
Code:
Private Sub FindButton_Click()
   Dim r As Range  
   Set r = Worksheets("Records").Range("A:A").Find(TransactionNoBox.Value)   ' This finds my value
    If Not r Is Nothing Then
       TransactionNumberForm.Hide
       DeleteConfirmationForm.Show
    Else
       MsgBox "Not Found"
    End If
    
End Sub
 

Logistix

Board Regular
Joined
Aug 23, 2006
Messages
77

ADVERTISEMENT

Hi Jindon,

Thanks for the code. Although I've had to re-adjust it a little and still havn't got it doing what I want:

Rich (BB code):
Dim r As Range
     
    Set r = Worksheets("Records").Range("A:A").Find(TransactionNoBox.Value)   ' This finds my value and sets to r
    
    If Not r Is Nothing Then
    
    MsgBox ("You have not entered a vaild transaction number")
    
    Else if ....     <---- I'm lost here


    Else

    Worksheets("Records").Range("A:A").Find(TransactionNoBox.Value).Select
    
    TransactionNumberForm.Hide
    DeleteConfirmationForm.Show  ' I select this value because in my next form "DeleteConfirmationForm, I allow the user to delete the selected line if they want to

After my "else if" i need an expression for "if not" in column range a:a. Can you see where I've gone wrong?

Thanks
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
1) If Not r Is Nothing means searched item is found.
therefore you are doing opposite..
You need to change If clause like
If r Is Nothing Then

2) Not sure what you want to do with ElseIf...

3) you don't need to select the cell anyway.

If you tell us the whole story, we could write the code and you can learn from it....
Rich (BB code):
Dim r As Range
     
    Set r = Worksheets("Records").Range("A:A").Find(TransactionNoBox.Value)   ' This finds my value and sets to r
    
    If r Is Nothing Then
    
    MsgBox ("You have not entered a vaild transaction number")
    
    Else if ....     <---- I'm lost here


    Else

        r.Select
    
    TransactionNumberForm.Hide
    DeleteConfirmationForm.Show  ' I select this value because in my next form "DeleteConfirmationForm, I allow the user to delete the selected line if they want to
 

Logistix

Board Regular
Joined
Aug 23, 2006
Messages
77

ADVERTISEMENT

Example, Say I have a 12345, 45678, 99999 in cells A3,A4,A5. And each of these cells have rows of data in there (that I like to call records).

Now I have created a userform that seaches for a record and deletes it in the next userform. But to get to the next userform I first have to locate & select the record.

So initally I used the following to search for the record, by selecting the cell:

Code:
Private Sub FindButton_Click()

Worksheets("Records").Range("A:A").Find(TransactionNoBox.Value).Select
    
    TransactionNumberForm.Hide
    DeleteConfirmationForm.Show

end sub

This worked well to select the starting cell I need, because in my next form I want to delete the record (the entire row)

Say I input 12345 into TransactionNoBox - it will locate the correct cell which positions me up for my delete (if confirmed) which is in the next form.

But If i go ahead and enter 123 or any other number/letter, I an error message. How can I combat this in the code you have supplied?

In my ammended code, if I leave TransactionNoBox blank then I get a message box (asking to try again). I want the same thing if an invalid number is entered by the user.

Thanks again
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Does DeleteConfirmationForm have any functionality other than confirm?
How about msgbox?

Code:
Private Sub FindButton_Click()
Dim r As Range
   Set r = Sheets("Records").Columns("a").Find(TransactionNoBox.Text)
   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

Board Regular
Joined
Aug 23, 2006
Messages
77
That works fine, except for example if I have a record "12345" and I enter only "123", or "12", or "1" it is still asking me whether I want to delete the first transaction number it finds beginning with the "123", or "12" etc.. Rather than the msgbox "Not Found"
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Ah....

Code:
Private Sub FindButton_Click()
Dim r As Range
   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
 

Forum statistics

Threads
1,141,757
Messages
5,708,355
Members
421,566
Latest member
7Nabisco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top