Thread: Different version:Userform to edit data

nohero,

Give this a try:

Private Sub cmdAdd_Click()
    
    Dim ws As Worksheet: Set ws = Worksheets("ProductData")
    Dim rngFound As Range: Set rngFound = ws.[A:A].Find(Me.txtSerial.Value)
    Dim iRow As Long
    If Not rngFound Is Nothing Then
        iRow = rngFound.Row
    Else
        iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End If
    
    ws.Cells(iRow, 1).Value = Me.txtSerial.Value
    ws.Cells(iRow, 2).Value = Me.cboCode.Value
    ws.Cells(iRow, 3).Value = Me.txtDate.Value
    ws.Cells(iRow, 4).Value = Me.txtDi.Value
    ws.Cells(iRow, 5).Value = Me.txtPo.Value
    ws.Cells(iRow, 6).Value = Me.txtEl.Value
    ws.Cells(iRow, 7).Value = Me.txtLo.Value
    
End Sub

Hope that helps,
~tigeravatar

Almost where I need to be, there's one small situational glitch. In my actual code the txtSerial isn't input by the user, it's pulled from another cell to show as a label in the userform so it cannot be edited in the userform itself by the user. So my code looks like this:

    Dim ws As Worksheet: Set ws = Worksheets("ProductData")
    Dim rngFound As Range: Set rngFound = ws.[A:A].Find(ActiveWorkbook.Worksheets("Main").Range("D3").Value)
    Dim iRow As Long
    If Not rngFound Is Nothing Then
        iRow = rngFound.Row
    Else
        iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End If
    ws.Cells(iRow, 1).Value = ActiveWorkbook.Worksheets("Main").Range("D3").Value
    ws.Cells(iRow, 2).Value = Me.cboCode.Value
    ws.Cells(iRow, 3).Value = Me.txtDate.Value
    ws.Cells(iRow, 4).Value = Me.txtDi.Value
    ws.Cells(iRow, 5).Value = Me.txtPo.Value
    ws.Cells(iRow, 6).Value = Me.txtEl.Value
    ws.Cells(iRow, 7).Value = Me.txtLo.Value

My problem comes from the format of the source cell "D3". It is in a 5-digit format that can have zeroes to start. The .Find function in the code is not requiring a perfect match. For example, when I have the number 01234 in cell D3 and submit the form it's finding serial 12345 and changing that row because it's searching for "1234" and finds it in the first 4 characters of the 12345 and that one comes first in the list.

How can I make sure the .Find function looks for the complete 5-digit code and only accepts a perfect match even when starting with zeroes?

I tried changing it to .Text but then it finds nothing at all and just creates a new row.

nohero,

Need to add the LookAt:=xlWhole to the .Find() method. Try the following:

Private Sub cmdAdd_Click()
    
    Dim ws As Worksheet: Set ws = Worksheets("ProductData")
    Dim rngFound As Range: Set rngFound = ws.[A:A].Find(What:=Sheets("Main").Range("D3").Value, LookAt:=xlWhole)
    Dim iRow As Long
    If Not rngFound Is Nothing Then
        iRow = rngFound.Row
    Else
        iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End If
    
    ws.Cells(iRow, 1).Value = Me.txtSerial.Value
    ws.Cells(iRow, 2).Value = Me.cboCode.Value
    ws.Cells(iRow, 3).Value = Me.txtDate.Value
    ws.Cells(iRow, 4).Value = Me.txtDi.Value
    ws.Cells(iRow, 5).Value = Me.txtPo.Value
    ws.Cells(iRow, 6).Value = Me.txtEl.Value
    ws.Cells(iRow, 7).Value = Me.txtLo.Value
    
End Sub

Thanks again! Works exactly as intended!