Sleeper: Troubleshooting Add Command

[Sir Phoenix]

Howdy!

Workin' on me userform, was digging through VBAX and found some code snippets. Tried to make me a useful Add button, and made this.


Private Sub cmdAdd_Click()
Dim rngLook As Range, rngFound As Range
If Me.cmbEmpName.Value = "" Or Me.cmbShiftType.Value = "" Or _
Me.cmbSchedType.Value = "" Then
MsgBox ("Please enter in all the information for this employee.")
Else
With ThisWorkbook.Sheets(5)
Set rngLook = .Range("A3", .Cells(Rows.Count, "A").End(xlUp))
Set rngFound = rngLook.Find(Me.cmbEmpName.Value, MatchCase:=True)
If rngFound Is Nothing Then
.Range("A" & rngLook.Count + 1).Value = Me.cmbEmpName.Value
.Range("B" & rngLook.Count + 1).Value = Me.cmbShiftType.Value
.Range("C" & rngLook.Count + 1).Value = Me.cmbSchedType.Value
MsgBox (cmbEmpName.Value & " has been added with a shift of " & _
cmbShiftType.Value & " and a schedule type of " & cmbSchedType.Value)
Else
.Range("B" & rngLook.Count).Value = Me.cmbShiftType.Value
.Range("C" & rngLook.Count).Value = Me.cmbSchedType.Value
End If
End With
End If
End Sub

Anywho, if the column A match does not exist, (and there's no records), it correctly gets placed in cells A3:C3. If a second record is added, instead of going to cells A4:C4, it overwrites A2:C2, taking out the column headers!

If there is a match, the button simply fails to respond.

Please help and thank yoo!

[Sir Phoenix]

Made a slight improvement. Now if the name exists in column A, the appropriate column B and C are updated. But if I try to add one at the Bottom, it overwrites the 2nd entry (cells A4:C4)

new code

Private Sub cmdAdd_Click()
Dim rngLook As Range, rngFound As Range
If Me.cmbEmpName.Value = "" Or Me.cmbShiftType.Value = "" Or _
Me.cmbSchedType.Value = "" Then
MsgBox ("Please enter in all the information for this employee.")
Else
With ThisWorkbook.Sheets(5)
Set rngLook = .Range("A3", .Cells(Rows.Count, "A").End(xlUp))
Set rngFound = rngLook.Find(Me.cmbEmpName.Value, MatchCase:=True)
If rngFound Is Nothing Then
.Range("A" & rngLook.Row + 1).Value = Me.cmbEmpName.Value
.Range("B" & rngLook.Row + 1).Value = Me.cmbShiftType.Value
.Range("C" & rngLook.Row + 1).Value = Me.cmbSchedType.Value
MsgBox (cmbEmpName.Value & " has been added with a shift of " & _
cmbShiftType.Value & " and a schedule type of " & cmbSchedType.Value)
Else
.Range("B" & rngFound.Row).Value = Me.cmbShiftType.Value
.Range("C" & rngFound.Row).Value = Me.cmbSchedType.Value
End If
End With
End If
End Sub

[mdmackillop]

Hi Sir Phoenix.

Can you attach a file with your form and some sample data? It makes life easier.
Regards
MD