Update data in sheet

[Jakesie]

Hi there,

Im new here, and Im working on a excell sheet that I use to generate invoices,, record them in a database, etc. I do not have any VBA knowledge, , so i'm learning here.
My question is this: I have a UserForm that is used to add and view customers. all is working fine, except that I need a code (or info) how to update existing info about users. I have the following code. Is there a way to alter it so it can update existing records instead of add to a new line?

Thanking yo in advance,

Jakes.

Private Sub CommandButton2_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("CustDATABASE")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.cboID.Value) = "" Then
  Me.cboID.SetFocus
  MsgBox "Please enter a User ID number"
  Exit Sub
End If

  If WorksheetFunction.CountIf(ws.Range("A2", ws.Cells(iRow, 1)), Me.cboID.Value) > 0 Then
        MsgBox "Duplicate Customer ID Found", vbCritical
        Exit Sub
    End If
    
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.cboID.Value
ws.Cells(iRow, 2).Value = Me.txtNAME.Value
ws.Cells(iRow, 3).Value = Me.txtSURNAME.Value
ws.Cells(iRow, 4).Value = Me.txtADRESS.Value
ws.Cells(iRow, 5).Value = Me.txtTOWN.Value
ws.Cells(iRow, 6).Value = Me.txtNUMBER.Value
ws.Cells(iRow, 7).Value = Me.txtEMAIL.Value
ws.Cells(iRow, 8).Value = Me.txtVAT.Value

'clear the data
Me.cboID.Value = ""
Me.txtNAME.Value = ""
Me.txtSURNAME.Value = ""
Me.txtADRESS.Value = ""
Me.txtTOWN.Value = ""
Me.txtNUMBER.Value = ""
Me.txtEMAIL.Value = ""
Me.txtVAT.Value = ""
Me.cboID.SetFocus
End Sub

 

[Jerry Sullivan]

Hello and Welcome,

You should be able to replace these lines (which say: if you find a matching ID, stop)

If WorksheetFunction.CountIf(ws.Range("A2", ws.Cells(iRow, 1)), Me.cboID.value) > 0 Then
    MsgBox "Duplicate Customer ID Found", vbCritical
    Exit Sub
End If

with this code (which says: if you find a matching ID, use that row instead of a new last row).

Dim c As Range
Set c = ws.Range("A2", ws.Cells(iRow, 1)).Find(What:=Me.cboID.value, _
    LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
    iRow = c.Row
    Set c = Nothing
End If

It's hard for me to test this without having a copy of your workbook. If it doesn't work, let me know and it shouldn't be too hard to debug.