Need Hlep with VB Excel Macro Please

[mollovg]

Hello Everyone,

I ma a novice to excel and VB. I want to make a program in an excel workbook that adds new client details to the workbook with an Input Box. So far I have achievded to add new client info bu it doesnt go to the new empty row below the last one I entered and I dont know why. All I wanted to do for the moment was to have client info be entered into the workbook but since I have column description at the top as well the information would have to be entered after the 11 row. I have been going at this for days now and cant figure out how to do it tried with For Next loop to add the info and a new column but doesnt work. Any help would be greatly appreciated.

I will paste the code I have so far here:

[VBA]Sub 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 lastrow
Dim c As Integer
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
ClientStat = "ES"
lastrow = Selection.Rows.Count
m = ActiveCell.Row
For i = m To lastrow
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
Next i
End Sub[/VBA]


Thnka you.

[Bob Phillips]

Rather than all of those workbooks, I would design a single userform to capture all of the details, much more user-friendly, or even get them to add it directly to the target sheet.

What is the selection when you run that drives lastrow etc.?

[mollovg]

Thanks for your reply. Well for last row the assumption is that because the 12th row is the last one that has descriptions of each field the netering of the client details would start from that 12th row, but it doesnt work actually the whole script doesnt work.

[mollovg]

Oh sorry the lastrow I am trying to make it start from the 12thy row and go until the last row but it doesnt work it just keeps overwriting the last row

[Bob Phillips]

Try

[vba]

Sub 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 lastrow
Dim c As Integer
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
ClientStat = "ES"
With Worksheets("Sheet2")

lastrow = .Range("A1").End(xlDown).Row

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")
.Range("A" & lastrow) = ClientStat
.Range("B" & lastrow) = StrCustRef
.Range("C" & lastrow) = StrCompName
.Range("D" & lastrow) = ContactName
.Range("E" & lastrow) = Telephone
.Range("F" & lastrow) = email
.Range("G" & lastrow) = ContDate
.Range("H" & lastrow) = DateSentOffice
.Range("I" & lastrow) = ClientReplyDate
.Range("J" & lastrow) = ListSentDate
.Range("K" & lastrow) = orders
End With
End Sub[/vba]

I would still use a form though.

[mollovg]

Thnkas for the code very much it still saves it one on top of another on top of the row I previously entered bu I will try and work on it Thanks again

[JWest]

The following code should work. I have also added an error control for when people enter information that conflicts with data type you are expecting (eg. someone enters "asdasdf" for a phone number, it will prompt them to enter a proper data type).

Another way to avoid this "Data Mismatch" problem is to change all of your variable declarations to "String" or "Variant" which allows the user to enter whatever type of data they want.

[VBA]
Sub 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 lastrow As String
Dim c As Integer
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
ClientStat = "ES"
With Worksheets("Sheet2")

lastrow = Range("A65536").End(xlUp).Offset(1, 0).Row

On Error GoTo ErrHandler

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")

GoTo SkipErr

ErrHandler:

MsgBox "You have entered incorrect information, please enter correct information"

Resume

SkipErr:


.Range("A" & lastrow) = ClientStat
.Range("B" & lastrow) = StrCustRef
.Range("C" & lastrow) = StrCompName
.Range("D" & lastrow) = ContactName
.Range("E" & lastrow) = Telephone
.Range("F" & lastrow) = email
.Range("G" & lastrow) = ContDate
.Range("H" & lastrow) = DateSentOffice
.Range("I" & lastrow) = ClientReplyDate
.Range("J" & lastrow) = ListSentDate
.Range("K" & lastrow) = orders
End With
End Sub
[/VBA]

[JWest]

Alternatively you can use this:

[VBA]
lastrow = Range("A1").End(xlDown).Offset(1, 0).Row
[/VBA]

Instead of using this:

[VBA]lastrow = Range("A65536").End(xlUp).Offset(1, 0).Row [/VBA]

The first way starts at the top and works its way down until it finds the last populated row. The second way starts at "A65536" and works its way up to find the last populated row.

[Aussiebear]

Rather than all of those workbooks, I would design a single userform to capture all of the details, much more user-friendly, or even get them to add it directly to the target sheet.

Excellent advice Bob. There have been a number of threads lately which require a method of capturing data and inputting to a sheet. The high level of control, when using a form, would eliminate most of their issues.