# Update existing row or add a new row if not found (Improvements needed)



## jackie21 (Dec 29, 2022)

Hi-  Still learning VBA...

I created a user form that links to the specific account that is being searched. 
The user form is used to update or add the customer interactions/contact info ect... for that specific account.
The code I have below works...but it is extremely slow. 
Any suggestions on how to improve the code would be very much appreciated.

Thank you



```
Private Sub UserForm_Initialize()

Dim account_number As String
account_number = Worksheets("SEARCH").Range("E11")
lastrow = Worksheets("Past_Communications").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow
If Worksheets("Past_Communications").Cells(i, 1).Value = account_number Then

Notes.Text = Worksheets("Past_Communications").Cells(i, 3).Value
CI.Text = Worksheets("Past_Communications").Cells(i, 4).Value
TS.Text = Worksheets("Past_Communications").Cells(i, 5).Value
SR.Text = Worksheets("Past_Communications").Cells(i, 6).Value


End If
Next

End Sub


Private Sub CommandButton1_Click()

Dim account_number As String
account_number = Worksheets("SEARCH").Range("E11")
lastrow = Worksheets("Past_Communications").Cells(Rows.Count, 1).End(xlUp).Row

Dim MIU As String
MIU = Worksheets("SEARCH").Range("E13")

Dim wsh As Worksheet
Set wsh = ThisWorkbook.Worksheets("Past_Communications")

Set tbl = wsh.ListObjects("Past_Comm")

Dim addRow As ListRow
Set addRow = tbl.ListRows.Add

For i = 2 To lastrow
If Worksheets("Past_Communications").Cells(i, 1).Value = account_number Then

Worksheets("Past_Communications").Cells(i, 3).Value = Notes.Text
Worksheets("Past_Communications").Cells(i, 4).Value = CI.Text
Worksheets("Past_Communications").Cells(i, 5).Value = TS.Text
Worksheets("Past_Communications").Cells(i, 6).Value = SR.Text


Else


With addRow

.Range(1) = account_number
.Range(2) = MIU
.Range(3) = Notes.Text
.Range(4) = CI.Text
.Range(5) = TS.Text
.Range(6) = SR.Text



End With
End If
Next

MsgBox "Saved", vbDefaultButton1, "Saved"


End Sub
```


----------



## NdNoviceHlp (Dec 29, 2022)

Hi jackie21. Add an "Exit For" like this...

```
SR.Text = Worksheets("Past_Communications").Cells(i, 6).Value
Exit For
```
HTH. Dave


----------



## Alex Blakenburg (Dec 30, 2022)

I might have totally missed the point here but see if the below helps.
Try it on a *copy *of your workbook.
Your code is adding an extra line to the table even if the account no is already there, I have assumed that is not correct.
I have also assumed that your account numbers are strings in your table, let me know if that is not the case.


```
Private Sub UserForm_Initialize()

Dim account_number As String
Dim lastrow As Long, acctRow As Long

account_number = Worksheets("SEARCH").Range("E11")
lastrow = Worksheets("Past_Communications").Cells(Rows.Count, 1).End(xlUp).Row

Dim wsh As Worksheet
Set wsh = ThisWorkbook.Worksheets("Past_Communications")

Dim tbl As ListObject
Set tbl = wsh.ListObjects("Past_Comm")

With Application
    acctRow = .IfError(.Match(account_number, tbl.ListColumns(1).Range, 0), 0)
End With

If acctRow <> 0 Then
    With tbl
        Notes.Text = .Range(acctRow, 3).Value
        CI.Text = .Range(acctRow, 4).Value
        TS.Text = .Range(acctRow, 5).Value
        SR.Text = .Range(acctRow, 6).Value
    End With
End If

End Sub

Private Sub CommandButton1_Click()

Dim account_number As String
Dim lastrow As Long, acctRow As Long

account_number = Worksheets("SEARCH").Range("E11")
lastrow = Worksheets("Past_Communications").Cells(Rows.Count, 1).End(xlUp).Row

Dim MIU As String
MIU = Worksheets("SEARCH").Range("E13")

Dim wsh As Worksheet
Set wsh = ThisWorkbook.Worksheets("Past_Communications")

Dim tbl As ListObject
Set tbl = wsh.ListObjects("Past_Comm")

With Application
    acctRow = .IfError(.Match(account_number, tbl.ListColumns(1).Range, 0), 0)
End With

If acctRow <> 0 Then
    With tbl
        .Range(acctRow, 3).Value = Notes.Text
        .Range(acctRow, 4).Value = CI.Text
        .Range(acctRow, 5).Value = TS.Text
        .Range(acctRow, 6).Value = SR.Text
    End With
Else
    Dim addRow As ListRow
    Set addRow = tbl.ListRows.Add
   
    With addRow
        .Range(1) = account_number
        .Range(2) = MIU
        .Range(3) = Notes.Text
        .Range(4) = CI.Text
        .Range(5) = TS.Text
        .Range(6) = SR.Text
    End With
End If

MsgBox "Saved", vbDefaultButton1, "Saved"

End Sub
```


----------

