0

I have a large table of items. The first column of the table is part number - this might be fully numerical (e.g. 605042) or it could be alphanumeric (XD-5201). The user inputs the part number into the cells.

I have a VBA code where I want the user to be able to enter a part number into a separate cell, run the code, and the row in the table with that matching part number will be deleted. So firstly I need to identify the row in the table that match the input cell value.

Dim deletePartRow As Integer
Dim deletePartColumn As Integer
Dim deletePartNumber As String
Dim topRowDelete As Long

deletePartRow = 14     'Defines the input cell for the user
deletePartColumn = 14  'Defines the input cell for the user

deletePartNumber = Cells(deletePartRow, deletePartColumn)  'Defines variable with user input part number

topRowDelete = Range("C:C").Find(deletePartNumber).Row   'Define the row that contains the part number

I would expect topRowDelete to return the Row where the matching part number is found, but instead I get a Run-time error 91: Object variable or With block variable not set.

What am I doing wrong?

Thanks

tectactoe
  • 21
  • 3
  • The `Find` didn't find any match. When using `Find`, always specify the `What`, `LookIn`, and `LookAt` parameters (you only specified `What`), and always [test if the Find succeeded](https://stackoverflow.com/questions/1589939/how-to-detect-whether-vba-excel-found-something). – BigBen Dec 05 '22 at 18:58
  • When using `Find()` it's a good idea to specify some of the the other arguments such as `LookAt` and `LookIn` - otherwise you get the settings from the last use of `Find()`, which may not be what you want. – Tim Williams Dec 05 '22 at 19:04
  • Thanks, adding these parameters seems to have fixed the problem. – tectactoe Dec 06 '22 at 13:46

0 Answers0