Find cell and delete row with cells.find

Sinon

Active Member
Joined
Aug 6, 2015
Messages
298
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:
Code:
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:
Code:
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
Code:
RefNo = Form.txtReferenceNumber.Value
with a simple
Code:
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Aug 6, 2015
Messages
298
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
Does this work
Code:
Dim fnd As Range
Set fnd = ActiveSheet.UsedRange.find(Val(RefNo), , xlWhole, , , , , , False)
If Not fnd Is Nothing Then fnd.EntireRow.Delete
 

Watch MrExcel Video

Forum statistics

Threads
1,109,250
Messages
5,527,633
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top