Need Help

[mollovg]

Hello,

I am new to Excel VB. I am trying to make a Excel workbook whereby through an InputBox a user can enter a new client and it asks for their details however it keeps entering new information on the same row as the last one, I dont know how to stop that and make it enter on a new row underneath the old one here is the code I have so far:


ub Button1_Click()
Dim emptyRow As Long

Dim i As Integer

Range("a1").Select
Range(Selection, Selection.End(xlDown)).Select
i = Selection.Rows.Count

'Make Sheet2 Active
Sheets(2).Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

Dim ClientStat As String
Dim StrCustRef As String
Dim StrCompName As String
Dim ContactName As String
Dim Telephone As Single
Dim email As String
Dim ContDate As Date
Dim DateSentOffice As Date
Dim ClientReplyDate As Date
Dim ListSentDate As Date
Dim orders As String
Dim linkOrders As String
If Sheets("Sheet2").Range("K8") = "Y" Then
Sheets("Sheet2").Range("L8") = "Click for Orders"
End If
With Range("A:K")
ClientStat = "ES"
StrCustRef = InputBox("Please enter Customer Reference")
StrCompName = InputBox("Please Enter Company Name")
ContactName = InputBox("Please enter the name of the person you were in contact with")
Telephone = InputBox("Please enter client telephone Number")
email = InputBox("Please enter client email Adress")
ListSentDate = InputBox("Please Enter the Date the List and/or presentation were sent to the client")
ContDate = InputBox("Please enter the date when the client replyied to the presentation")
DateSentOffice = InputBox("Please enter the date the client request was sent to the office")
ClientReplyDate = InputBox("Please enter the date the client replied")
orders = InputBox("Orders Y/N")
Sheets("Sheet2").Range("A8") = ClientStat
Sheets("Sheet2").Range("B8") = StrCustRef
Sheets("Sheet2").Range("C8") = StrCompName
Sheets("Sheet2").Range("D8") = ContactName
Sheets("Sheet2").Range("E8") = Telephone
Sheets("Sheet2").Range("F8") = email
Sheets("Sheet2").Range("G8") = ContDate
Sheets("Sheet2").Range("H8") = DateSentOffice
Sheets("Sheet2").Range("I8") = ClientReplyDate
Sheets("Sheet2").Range("J8") = ListSentDate
Sheets("Sheet2").Range("K8") = orders
Dim last_row As Long
last_row = Range("A65536").End(xlUp).Row
Range("A10:K10").Copy
ActiveSheet.Paste Destination:=Range("A" & last_row + 1)
Range("A10:K10").ClearContents
Application.CutCopyMode = False
End With
End Sub

What I really want to do is for new client details to go on a new row, and to keep the old now in there. I am stuck so I would really appreciate help. Thanks

 

[cicconmr]

COunt sometimes doesn't work on strings so make sure you are counting in a column that includes numbers.

Then, since you define i as the count, you want to do this before showing the input boxs:

i = i + 1

Fill out input boxes...

Then, to enter things in the appropriate cell you do as follows (just one example):

Sheets("Sheet2").Range("A" & i) = ClientStat

 

[mollovg]

Thanks for your quick reply and for the help very much appreciated. Just one question though I still keep the rest of the code as it is dont I? I mean the with command?

 

[mollovg]

Hi,

I still need help on some of the coding I have done it with For and Next but it still replaces the client details that I already have put in. Anyone please help. Here is the code:


ub Button1_Click()
Dim emptyRow As Long

Dim i As Integer
Dim n As Integer
Dim m As Long
Dim a As Integer
'Make Sheet2 Active
Sheets(2).Activate

Dim ClientStat As String
Dim StrCustRef As String
Dim StrCompName As String
Dim ContactName As String
Dim Telephone As Single
Dim email As String
Dim ContDate As Date
Dim DateSentOffice As Date
Dim ClientReplyDate As Date
Dim ListSentDate As Date
Dim orders As String
Dim linkOrders As String
If Sheets("Sheet2").Range("K8") = "Y" Then
Sheets("Sheet2").Range("L8") = "Click for Orders"
End If
ClientStat = "ES"
m = ActiveCell.Row
For i = 12 To m
StrCustRef = InputBox("Please enter Customer Reference")
StrCompName = InputBox("Please Enter Company Name")
ContactName = InputBox("Please enter the name of the person you were in contact with")
Telephone = InputBox("Please enter client telephone Number")
email = InputBox("Please enter client email Adress")
ListSentDate = InputBox("Please Enter the Date the List and/or presentation were sent to the client")
ContDate = InputBox("Please enter the date when the client replyied to the presentation")
DateSentOffice = InputBox("Please enter the date the client request was sent to the office")
ClientReplyDate = InputBox("Please enter the date the client replied")
orders = InputBox("Orders Y/N")
Sheets("Sheet2").Range("A" & i) = ClientStat
Sheets("Sheet2").Range("B" & i) = StrCustRef
Sheets("Sheet2").Range("C" & i) = StrCompName
Sheets("Sheet2").Range("D" & i) = ContactName
Sheets("Sheet2").Range("E" & i) = Telephone
Sheets("Sheet2").Range("F" & i) = email
Sheets("Sheet2").Range("G" & i) = ContDate
Sheets("Sheet2").Range("H" & i) = DateSentOffice
Sheets("Sheet2").Range("I" & i) = ClientReplyDate
Sheets("Sheet2").Range("J" & i) = ListSentDate
Sheets("Sheet2").Range("K" & i) = orders
Rows(m + i).Insert
Next i
End Sub