Updating existing data with UserForm

[Big Lar]

I can’t figure this out…<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Instead of replacing existing data, this code is adding a new row of data.

<o></o>
<o></o>
<o></o>

[FONT=Verdana][SIZE=1]Private Sub CommandButton1_Click()[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Dim iRow        As Long<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Dim WS          As Worksheet<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Dim FullName    As String<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Dim CLoc        As Range<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Set WS = Worksheets("Sheet1")<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]FullName = Me.ComboBox1.Value<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Set CLoc = WS.Columns("D:D").Find(What:=FullName, After:=WS.Cells(1, 4), LookIn:= _<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]                     xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]                     xlNext, MatchCase:=False, SearchFormat:=False)<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]If CLoc Is Nothing Then<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1] iRow = WS.Cells(Rows.Count, 1) _<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]        .End(xlUp).Offset(1, 0).Row<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Else<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1] iRow = CLoc.Row<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]End If<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]'copy the data to the database<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]WS.Cells(iRow, 1).Value = Me.TextBox14.Value<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]WS.Cells(iRow, 2).Value = Me.TextBox41.Value<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]WS.Cells(iRow, 3).Value = Me.TextBox42.Value<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]WS.Cells(iRow, 4).Value = Me.TextBox42.Value & " " & Me.TextBox41.Value<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]WS.Cells(iRow, 5).Value = Me.TextBox1.Value<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]WS.Cells(iRow, 6).Value = Me.TextBox2.Value<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]WS.Cells(iRow, 7).Value = Me.TextBox3.Value[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]'etc...etc...[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]'clear the data from the textboxes<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Me.ComboBox1.Value = ""<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Me.TextBox14.Value = ""<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Me.TextBox41.Value = ""<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Me.TextBox42.Value = ""<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Me.TextBox42.Value = ""<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Me.TextBox1.Value = ""<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Me.TextBox2.Value = ""<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Me.TextBox3.Value = ""[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]'etc...etc...<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]Range("A2:AZ" & iRow).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("B2"), Order1:=xlAscending, Header:= _<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]                        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]                        DataOption1:=xlSortNormalHeader<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]
[SIZE=1][FONT=Verdana]End Sub[/FONT][/SIZE]

<o></o>

 

[JamesW]

Hi,

you are going the the last row then offsetting it by 1, try this instead:

 Dim iRow        As Long
Dim WS          As Worksheet
Dim FullName    As String
Dim CLoc        As Range
Set WS = Worksheets("Sheet1")
FullName = Me.ComboBox1.Value

Set CLoc = WS.Columns("D:D").Find(What:=FullName, After:=WS.Cells(1, 4), LookIn:= _
                     xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
                     xlNext, MatchCase:=False, SearchFormat:=False)

If CLoc Is Nothing Then
 [U][B]iRow = WS.Cells(Rows.Count, 1) _
        .End(xlUp).Row[/B][/U]
Else
 iRow = CLoc.Row
End If

'copy the data to the database
WS.Cells(iRow, 1).Value = Me.TextBox14.Value
WS.Cells(iRow, 2).Value = Me.TextBox41.Value
WS.Cells(iRow, 3).Value = Me.TextBox42.Value
WS.Cells(iRow, 4).Value = Me.TextBox42.Value & " " & Me.TextBox41.Value
WS.Cells(iRow, 5).Value = Me.TextBox1.Value
WS.Cells(iRow, 6).Value = Me.TextBox2.Value
WS.Cells(iRow, 7).Value = Me.TextBox3.Value
'etc...etc...

 

[Rick Rothstein]

[FONT=Verdana][SIZE=1]Set CLoc = WS.Columns("D:D").Find(What:=FullName, After:=WS.Cells(1, 4), LookIn:= _<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]                   xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]                   xlNext, MatchCase:=False, SearchFormat:=False)<o:p></o:p>[/SIZE][/FONT]
[FONT=Verdana][SIZE=1]<o:p></o:p>[/SIZE][/FONT]

<o></o>

Just a guess... try replacing the xlFormulas in the above code line with xlValues and see if that makes your code work.

 

[Big Lar]

Just a guess... try replacing the xlFormulas in the above code line with xlValues and see if that makes your code work.

Ding Ding Ding...we have a winner!
Thanks so much Rick!