GoTo Next Line

[trevmac4]

Hi I am building a worksheet for customer tracking and I am wanting to build a form that would allow me to input customer info and the info would be input into the next open line. As of now I have customer names (A2-A57), contact (B2-B57), address (C2-C57) so I want the net entry from my form to automatically be put in line 57, any ideas on the formula for this one?

Also, if i wanted to "Edit" a customer how could I build formula to go to this "match" from my list?

Thank you in advance.

Trevor

 

[Smitty]

Welcome to the Board!

You can use the Last Row method. E.G.

Dim lr as long
Dim ws as Worksheet

  Set ws = Sheets("Sheet1")

  lr = ws.Cells(Rows.Count,"A").End(xlUp).Offset(1).Row

  With ws
     .Cells(lr,"A").Value = Me.TextBox1.Value
     .Cells(lr,"B").Value = Me.TextBox2.Value
     .Cells(lr,"C").Value = Me.TextBox3.Value
  End with

If you PM me your e-mail address I'll see if I can dig up a fillable/searchable Customer Contact form that I did a while back.

HTH,

 

[L. Howard]

Hi Trevor,

I opted for a vba solution of what I "THINK" you want to do.
Try this and perhaps it will get you going.
I assigned short-cut key strokes to each macro...'ctrl + a & 'ctrl + e.

Option Explicit
Sub CustContAdd()
'ctrl + a
Dim CName As String
Dim CTact As String
Dim CAdd As String
CName = InputBox("  Enter customer name")
Range("A1000").End(xlUp).Offset(1, 0) = CName
CTact = InputBox("  Enter contact name")
Range("B1000").End(xlUp).Offset(1, 0) = CTact
CAdd = InputBox("  Enter customer address")
Range("C1000").End(xlUp).Offset(1, 0) = CAdd
End Sub

Sub EditMySyuff()
'ctrl + e
Dim MyEdit As String
Dim Msg, Style, Title, Response
Msg = "Do you want edit list ?" 
Style = vbYesNo 
Title = "EditMySyuff"   
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then   
    MyEdit = InputBox("  Enter customer name") 
        Cells.Find(What:=MyEdit, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
  Else   
    Exit Sub    
End If
End Sub

Regards,
Howard

 

[trevmac4]

Thanks Howard,
Greatly appreciate the option you provided!

Trevor