Find cell and delete row with cells.find

[Sinon]

Hello all,

I've a question about cells.find. I know I can use different code to find a cell with a specific value and then delete its row but I rather like the simplicity of cells.find.

My code is as follows:

Dim RefNo As Long

    RefNo = Form.txtReferenceNumber.Value

    ThisWorkbook.Worksheets("Sheet1").Activate
    Range("A1").Activate
    
        ActiveCell.End(xlDown).Offset(1, 0).Select
        ActiveCell.Value = RefNo

    'Other bits of code that work

    ThisWorkbook.Worksheets("Sheet1").Activate
    ProductCat = WorksheetFunction.Application.WorksheetFunction.VLookup(RefNo, ThisWorkbook.Worksheets("Sheet1").Range("A:AJ"), 36, False)
    If ProductCat = "Product1" Then
    
    Cells.Find(What:=RefNo).Select
    Selection.EntireRow.Delete
                            Else
                            'Do nothing
    End If

The form has this code for the RefNo field:

Private Sub txtReferenceNumber_Change()


'Centre Text Alignment
    txtReferenceNumber.TextAlign = fmTextAlignCenter


End Sub

As you can see, I have a form that pops up when the button is clicked on in the sheet and this works perfectly well. I was recently asked to change this code a bit and need to delete a line that I create in the code. The problem is, the above code errors out at line "Cells.Find(What:=RefNo).Select". The error is "run time error 91 object variable or with block variable not set"

Looking online, the reason seems to be that the cells.find cannot find the value I am looking for, RefNo. But I can see the value in the sheet (and the Vlookup works too) and if I replace the line

RefNo = Form.txtReferenceNumber.Value

with a simple

RefNo="123"

and Step into the code (F8) then it works. I though maybe the RefNo from the form isn't numeric but I checked with an IsNumeric() and it's a number, same as the spreadsheet. I haven't really used cells.find before but everything except the "What" is optional so I don't think I am missing something from its syntax. Can't figure out what the problem is. Any ideas?

Thanks,
Sinon

 

[Fluff]

Whilst the parameters in .Find are optional, Xl/VBA remembers the last used settings, so it's always best to specify them where needed.
For instance if you have 1234 in a cell, then finding 123 will give a match if if the current setting is xlpart, but will return an error with xlwhole.

That said, if the code works with RefNo="123" then the value is a string, not a number

 

[Sinon]

Whilst the parameters in .Find are optional, Xl/VBA remembers the last used settings, so it's always best to specify them where needed.
For instance if you have 1234 in a cell, then finding 123 will give a match if if the current setting is xlpart, but will return an error with xlwhole.

That said, if the code works with RefNo="123" then the value is a string, not a number

Thanks Fluff. Your point about the xlpart/xlwhole makes sense and I will add xlwhole to ensure the correct line is picked up.

RefNo is Long and using a boolean variable to see if ISNUMERIC(RefNo) I can see it's true. I tried RefNo=123 and it works when I step in, same as with "123". I don't think that's the problem. The number in the sheet is also a number (ISNUMBER() results in TRUE). I think the vlookup would fail if that were the case but the vlookup works fine.
I tried setting RefNo as Integer and the vlookup works but not the cells.find. What is odd to me is that the vlookup works but not the cells.find.

 

[Fluff]

Is the number in the cell a hard value, or the result of a formula?
Also IsNumeric doesn't always work as you might imagine, have a look at this for more info. Thinking About Using VBA's IsNumeric Function? Read this first.

 

[Sinon]

Is the number in the cell a hard value, or the result of a formula?
Also IsNumeric doesn't always work as you might imagine, have a look at this for more info. Thinking About Using VBA's IsNumeric Function? Read this first.

The number in the sheet is a hard value, copied from another sheet with VBA.
If IsNumeric doesn't work then I'm not sure how I can check it.

 

[Fluff]

Does this work

Dim fnd As Range
Set fnd = ActiveSheet.UsedRange.find(Val(RefNo), , xlWhole, , , , , , False)
If Not fnd Is Nothing Then fnd.EntireRow.Delete