this code was working fine yesterday. at least i think i remember it working
as i finished a 20hr shift. any ways... it isn't now, and i have no idea
why. It is supposed to add a name and number from a userform to a list on
sheet2. Now it overwrites row 2 in either sheet, depending on which is active
when i run the userform.

any advice would be great.

Thanks in advance

Private Sub CommandButton2_Click()
With ActiveWorkbook.Worksheets("sheet2")
If TextBox1.Text < "" And Not .Range("A:A").Find(TextBox1.Text,
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
MsgBox "This Officer's name already exists on file. "
TextBox1.Text = Clear
Cancel = True
Else
Range("A:A").End(xlUp)(2).Select
Application.Selection.Value = TextBox1.Text
Cancel = False
If TextBox2.Text < "" And Not .Range("C:C").Find(TextBox2.Text,
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
MsgBox "This Officer's Number already exists on file. "
TextBox1.Text = Clear
Cancel = True
Else
Range("C:C").End(xlUp)(2).Select
Application.Selection.Value = TextBox2.Text
Cancel = False
End If
End If
End With

End Sub

Hi,


Change:

Range("A:A").End(xlUp)(2).Select
Application.Selection.Value = TextBox1.Text

to
..Cells(Rows.Count, "A").End(xlUp)(2) = TextBox1.Text

and repeat ...

..Cells(Rows.Count,"C").End(xlUp)(2) = TextBox2.Text

HTH

"jeramie" wrote:

this code was working fine yesterday. at least i think i remember it working
as i finished a 20hr shift. any ways... it isn't now, and i have no idea
why. It is supposed to add a name and number from a userform to a list on
sheet2. Now it overwrites row 2 in either sheet, depending on which is active
when i run the userform.

any advice would be great.

Thanks in advance

Private Sub CommandButton2_Click()
With ActiveWorkbook.Worksheets("sheet2")
If TextBox1.Text < "" And Not .Range("A:A").Find(TextBox1.Text,
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
MsgBox "This Officer's name already exists on file. "
TextBox1.Text = Clear
Cancel = True
Else
Range("A:A").End(xlUp)(2).Select
Application.Selection.Value = TextBox1.Text
Cancel = False
If TextBox2.Text < "" And Not .Range("C:C").Find(TextBox2.Text,
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
MsgBox "This Officer's Number already exists on file. "
TextBox1.Text = Clear
Cancel = True
Else
Range("C:C").End(xlUp)(2).Select
Application.Selection.Value = TextBox2.Text
Cancel = False
End If
End If
End With

End Sub

thanks a bunch toppers!


"Toppers" wrote:

Hi,


Change:

Range("A:A").End(xlUp)(2).Select
Application.Selection.Value = TextBox1.Text

to
.Cells(Rows.Count, "A").End(xlUp)(2) = TextBox1.Text

and repeat ...

.Cells(Rows.Count,"C").End(xlUp)(2) = TextBox2.Text

HTH

"jeramie" wrote:

this code was working fine yesterday. at least i think i remember it working
as i finished a 20hr shift. any ways... it isn't now, and i have no idea
why. It is supposed to add a name and number from a userform to a list on
sheet2. Now it overwrites row 2 in either sheet, depending on which is active
when i run the userform.

any advice would be great.

Thanks in advance

Private Sub CommandButton2_Click()
With ActiveWorkbook.Worksheets("sheet2")
If TextBox1.Text < "" And Not .Range("A:A").Find(TextBox1.Text,
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
MsgBox "This Officer's name already exists on file. "
TextBox1.Text = Clear
Cancel = True
Else
Range("A:A").End(xlUp)(2).Select
Application.Selection.Value = TextBox1.Text
Cancel = False
If TextBox2.Text < "" And Not .Range("C:C").Find(TextBox2.Text,
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
MsgBox "This Officer's Number already exists on file. "
TextBox1.Text = Clear
Cancel = True
Else
Range("C:C").End(xlUp)(2).Select
Application.Selection.Value = TextBox2.Text
Cancel = False
End If
End If
End With

End Sub