Enter data in next empty row after cell name

[Guest]

I need some help entering data from a form. I have the sheet activaced and
want to select the next row after a paticular name and past information from
that cell.
I hope this is clear: Here is what i have.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim LastRow As Object
Set ws = ThisWorkbook.ActiveSheet()
Set LastRow = ws.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = txtCust.Text
LastRow.Offset(1, 1).Value = txtService.Text
LastRow.Offset(1, 2).Value = txtCircuit.Text

Thanks -mark

 

[Tom Ogilvy]

Private Sub cmdAdd_Click()
Dim LastRow As Range
Dim ws as Worksheet
Set ws = ActiveSheet
LastRow = ws.columns(1).Find("NametoSearchfor")
if not LastRow is nothing then
LastRow.offset(1,0).EntireRow.Insert
LastRow.Offset(1, 0).Value = txtCust.Text
LastRow.Offset(1, 1).Value = txtService.Text
LastRow.Offset(1, 2).Value = txtCircuit.Text
End if
End Sub

change NametoSearchfor to the actual value in the cell you are looking for

 

[Guest]

Thanks for your reply Tom:
I was wanting to fill the "name to search for" with the value from the
ComboBox.
Thanks again for your reply
-mark

 

[Tom Ogilvy]

That was your first mention of the word "Combobox". Assuming the combobox
is named combobox1, then

Private Sub cmdAdd_Click()
Dim LastRow As Range
Dim ws as Worksheet
Set ws = ActiveSheet
if len(trim(Combobox1.Value)) = 0 then exit sub
LastRow = ws.columns(1).Find(Combobox1.Value)
if not LastRow is nothing then
LastRow.offset(1,0).EntireRow.Insert
LastRow.Offset(1, 0).Value = txtCust.Text
LastRow.Offset(1, 1).Value = txtService.Text
LastRow.Offset(1, 2).Value = txtCircuit.Text
End if
End Sub

Change to suit your situation.

 

[Guest]

Tom Thanks so much for your help, I am new at this and you don't know how
long i have been working on it. The only thing is i had to add the Set to
the LastRow..
I have been reading this discussion group and have leared a lot from
everyones wisdom. Final Code:

Private Sub cmdAdd_Click()
Dim LastRow As Range
Dim ws As Worksheet
Set ws = ActiveSheet
If Len(Trim(cboSwitch.Value)) = 0 Then Exit Sub
Set LastRow = ws.Columns(1).Find(cboSwitch.Value)
If Not LastRow Is Nothing Then
LastRow.Offset(1, 0).EntireRow.Insert
LastRow.Offset(1, 0).Value = txtCust.Text
LastRow.Offset(1, 1).Value = txtService.Text
LastRow.Offset(1, 2).Value = txtCircuit.Text
End If
End Sub

 

[Tom Ogilvy]

Yes, it should have been set. Guess I overlooked it. My apologies.

 

[Guest]

Tom if you are still watching this post i have another question.
I want to be able to add a blank row if there is data in the next row.
Thanks -mark

 

[Tom Ogilvy]

this command should already do that:
LastRow.Offset(1, 0).EntireRow.Insert


do you mean you don't want to insert a blank row if the next row is empty

Private Sub cmdAdd_Click()
Dim LastRow As Range
Dim ws As Worksheet
Set ws = ActiveSheet
If Len(Trim(cboSwitch.Value)) = 0 Then Exit Sub
Set LastRow = ws.Columns(1).Find(cboSwitch.Value)
If Not LastRow Is Nothing Then
if lastrow.Offset(1,0).Value <> "" then _
LastRow.Offset(1, 0).EntireRow.Insert
LastRow.Offset(1, 0).Value = txtCust.Text
LastRow.Offset(1, 1).Value = txtService.Text
LastRow.Offset(1, 2).Value = txtCircuit.Text
End If
End Sub

 

[Guest]

yes if the row is already empty i don't want to insert another one,
Thanks

 

[Guest]

Tom all is working well. You are the Man! Thanks for all your help