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