Data Entry Form : VBA Run-time Error '91': Object Variable not set with ws.cells.find function

ChevOKeefe

New Member
Joined
Apr 25, 2018
Messages
4
Hi All,

I'm relatively new to VBA, and by no means a coder, so apologies in advance for any VBA/coding illiteracy in the following.

Using an amalgamation of online code and trial and error, I have created (almost) a data entry form that allows me to add entries to a worksheet. The form checks the worksheet for existing entries of the same information (based on a sub component and Part location number) and then prompts the user to use an "Update" function if the combination of these entries already exists. I've created a column, Column R, in my wokrsheet that concatenates these two values to create a unique identifier. I've created a string variable (CheckRow) that takes the inputs required on the form (me.cbopart.Value and me.combobox1.value) to create the same unique identifier (A combination of both) that can be searched against. This all works fine, but i'm having issues with the update function.

This uses a ws.cells.find to define the row number (lRow) as the result of a search of the worksheet for the predefined string variable (CheckRow), which will be in column R if it exists already.

I then use an IF condition to either update the record or notify the user that the entry already exists and to use the add function instead. See below code. It seems to fall down at defining lRow step when this returns nothing, despite it seeming to work fine on Friday when i last used it. (the If Irow = 0 Then... seemed to resolve then when getting the same message).

When i hover over the line of code in debugger, it appears to be giving me the correct values. ie. its returning the correct value for "CheckRow" and also showing lRow = 0, so i've no idea where this is failing. What is extra strange is that this appeared to be working last week when i left it, and i don't recall amending cell formats etc, before saving. Note that this sub is part of a greater scope within the data entry from vba, but believe the extract below to be pertinent to the problem. Any help would be greatly appreciated.

Code:
Private Sub cmdClose_Click()Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Dim CheckRow As String
Set ws = Worksheets("PartsData")
    
CheckRow = Me.cboPart.Value & Me.ComboBox1.Value
lPart = Me.cboPart.ListIndex
lRow = ws.Cells.Find(What:=CheckRow, SearchOrder:=xlRows, LookIn:=xlValues).Row

'Here is where it the debugger falls over with the run-time error, despite seeming to work with exactly the same code a few days previous. (When lRow returns 0)


'check for a Sub component
If Trim(Me.cboPart.Value) = "" Then
  Me.cboPart.SetFocus
  MsgBox "Please select a sub component"
  Exit Sub
End If


'check for a Wind Turbine number
If Trim(Me.ComboBox1.Value) = "" Then
  Me.ComboBox1.SetFocus
  MsgBox "Please enter a Wind Turbine"
  Exit Sub
  
'check if Entry exists
End If


If lRow = 0 Then
MsgBox "Entry does not Exist for " & Me.cboPart.Value & " on " & Me.ComboBox1.Value & " - Use Add Function", vbCritical
Else
ws.Cells(lRow, 4).Value = Me.cboPart.Value
  ws.Cells(lRow, 5).Value = Me.cboType.Value
  ws.Cells(lRow, 6).Value = Me.cboPart.List(lPart, 1)
  ws.Cells(lRow, 22).Value = Me.cboStatus.Value
  ws.Cells(lRow, 8).Value = Me.txtDate.Value
  ws.Cells(lRow, 9).Value = Me.txtQty.Value
  ws.Cells(lRow, 10).Value = Me.ComboBox1.Value
  ws.Cells(lRow, 11).Value = Me.ComboBox2.Value
  ws.Cells(lRow, 2).Value = Application.UserName
  ws.Cells(lRow, 3).Value = Now
  ws.Cells(lRow, 3).NumberFormat = "mm/dd/yyyy hh:mm:ss"
End If






'clear the data
'ClearParts
Me.cboType.Value = ""
Me.cboPart.Value = ""
Me.cboPart.RowSource = ""
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""






Me.cboStatus.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboType.SetFocus
Me.ComboBox2.SetFocus


End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try
Code:
Private Sub cmdClose_Click()
Dim Fnd As Range
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Dim CheckRow As String
Set ws = Worksheets("PartsData")
    
CheckRow = Me.cbopart.Value & Me.ComboBox1.Value
lPart = Me.cbopart.ListIndex
Set Fnd = ws.Range("R:R").Find(CheckRow, , , xlWhole, , , False, , False)



'check for a Sub component
If Trim(Me.cbopart.Value) = "" Then
  Me.cbopart.SetFocus
  MsgBox "Please select a sub component"
  Exit Sub
End If


'check for a Wind Turbine number
If Trim(Me.ComboBox1.Value) = "" Then
  Me.ComboBox1.SetFocus
  MsgBox "Please enter a Wind Turbine"
  Exit Sub
  
'check if Entry exists
End If


If Fnd Is Nothing Then
   MsgBox "Entry does not Exist for " & Me.cbopart.Value & " on " & Me.ComboBox1.Value & " - Use Add Function", vbCritical
Else
   lRow = Fnd.Row
   ws.Cells(lRow, 4).Value = Me.cbopart.Value
   ws.Cells(lRow, 5).Value = Me.cboType.Value
   ws.Cells(lRow, 6).Value = Me.cbopart.list(lPart, 1)
   ws.Cells(lRow, 22).Value = Me.cboStatus.Value
   ws.Cells(lRow, 8).Value = Me.txtDate.Value
   ws.Cells(lRow, 9).Value = Me.txtQty.Value
   ws.Cells(lRow, 10).Value = Me.ComboBox1.Value
   ws.Cells(lRow, 11).Value = Me.ComboBox2.Value
   ws.Cells(lRow, 2).Value = Application.UserName
   ws.Cells(lRow, 3).Value = Now
   ws.Cells(lRow, 3).NumberFormat = "mm/dd/yyyy hh:mm:ss"
End If






'clear the data
'ClearParts
Me.cboType.Value = ""
Me.cbopart.Value = ""
Me.cbopart.RowSource = ""
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""






Me.cboStatus.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = 1
Me.cboType.SetFocus
Me.ComboBox2.SetFocus


End Sub
If the value cannot be found, then your code will crash because you cannot get the row number on a non-existing cell
 
Upvote 0
Fluff,

Worked perfectly, and makes sense, though no idea why it seemed to be working on Friday if the above is the case? I had a column hidden that I unhid and then it worked.

Thanks for your help,

Much Appreciated.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,617
Latest member
Narendra Babu D

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
Back
Top