Return Data to next empty line of Worksheet

M

mssianlf

New Member
Joined
Aug 24, 2011
Messages
15
Help!!
I have been tasked with making a user form that gathers basic information and enters in into an excel worksheet, I have created and coded the below but cant make the data return to the next empty line it just over writes the previous entry. I have included the whole code.As I don't really know what I am doing any help would be gratefully received but if it could be explained in small simple words I would be grateful.

Private Sub cmdcancel_Click()
Unload Me
End Sub

Private Sub cmdclear_Click()
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Private Sub cmdok_Click()

Dim RowCount As Long
Dim Control As Control

If Me.txtCRM.Value = "" Then
MsgBox "Please enter a CRM Name.", vbExclamation, "txtinitialyear"
Me.txtCRM.SetFocus
Exit Sub
End If
If Me.txtconame.Value = "" Then
MsgBox "Please enter a Company Name.", vbExclamation, "txtcontactname"
Me.txtconame.SetFocus
Exit Sub
End If
If Me.txtemail.Value = "" Then
MsgBox "Please enter an Email Address.", vbExclamation, "txtphone"
Me.txtemail.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtyearvalue.Value) Then
MsgBox "The Year Value box must contain a number.", vbExclamation, "txtdealvalue"
Me.txtyearvalue.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtdealvalue.Value) Then
MsgBox "The Deal Value box must contain a number.", vbExclamation, "chksplitpayment"
Me.txtdealvalue.SetFocus
Exit Sub
End If
'find first empty row in database'
RowCount = Worksheets("Egold Invoices Payment Details").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Egold Invoices").Range("A2")
.Offset(RowCount, 0).Value = Me.txtCRM.Value
.Offset(RowCount, 1).Value = Me.txtconame.Value
.Offset(RowCount, 2).Value = Me.txtcontactname.Value
.Offset(RowCount, 3).Value = Me.txtemail.Value
.Offset(RowCount, 4).Value = Me.txtphone.Value
.Offset(RowCount, 5).Value = Me.cobxegoldtype.Value
.Offset(RowCount, 15).Value = Me.txtyearvalue.Value
.Offset(RowCount, 16).Value = Me.txtdealvalue.Value
.Offset(RowCount, 17).Value = DateValue(Me.calstartdate.Value)
.Offset(RowCount, 18).Value = DateValue(Me.calenddate.Value)
.Offset(RowCount, 19).Value = Me.txtinitialyear.Value
.Offset(RowCount, 20).Value = Me.txtnotes.Value
If Me.chkgenhr.Value = True Then
.Offset(RowCount, 6).Value = "P"
Else
.Offset(RowCount, 6).Value = "X"
End If
If Me.chkukcb.Value = True Then
.Offset(RowCount, 7).Value = "P"
Else
.Offset(RowCount, 7).Value = "X"
End If
If Me.chkuktd.Value = True Then
.Offset(RowCount, 8).Value = "P"
Else
.Offset(RowCount, 8).Value = "X"
End If
If Me.chkrecres.Value = True Then
.Offset(RowCount, 9).Value = "P"
Else
.Offset(RowCount, 9).Value = "X"
End If
If Me.chkfd.Value = True Then
.Offset(RowCount, 10).Value = "P"
Else
.Offset(RowCount, 10).Value = "X"
End If
If Me.chkboard.Value = True Then
.Offset(RowCount, 11).Value = "P"
Else
.Offset(RowCount, 11).Value = "X"
End If
If Me.chkukall.Value = True Then
.Offset(RowCount, 12).Value = "P"
Else
.Offset(RowCount, 12).Value = "X"
End If
If Me.chkeurohr.Value = True Then
.Offset(RowCount, 13).Value = "P"
Else
.Offset(RowCount, 13).Value = "X"
End If
If Me.chknew.Value = True Then
.Offset(RowCount, 14).Value = "N"
Else
.Offset(RowCount, 14).Value = ""
End If
If Me.chkrenew.Value = True Then
.Offset(RowCount, 14).Value = "R"
Else
.Offset(RowCount, 14).Value = ""
End If
If Me.chkwinback.Value = True Then
.Offset(RowCount, 14).Value = "W"
Else
.Offset(RowCount, 14).Value = ""
End If
With Worksheets("Egold Invoices Payment Details").Range("A2")
If Me.chksplitpayment.Value = True Then
.Offset(RowCount, 0).Value = "Yes"
Else
.Offset(RowCount, 0).Value = "No"
End If
.Offset(RowCount, 1).Value = Me.txtsp1.Value
.Offset(RowCount, 2).Value = Me.txtsp2.Value
.Offset(RowCount, 3).Value = Me.txtsp3.Value
.Offset(RowCount, 4).Value = Me.txtsp4.Value
.Offset(RowCount, 5).Value = Me.txtsp5.Value
.Offset(RowCount, 6).Value = Me.txtsp6.Value
.Offset(RowCount, 7).Value = Me.txtsp7.Value
.Offset(RowCount, 8).Value = Me.txtsp8.Value
.Offset(RowCount, 9).Value = Me.txtsp9.Value
.Offset(RowCount, 10).Value = Me.txtsp10.Value
.Offset(RowCount, 11).Value = Me.txtsp11.Value
.Offset(RowCount, 12).Value = Me.txtsp12.Value
.Offset(RowCount, 7).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
End With
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End With
End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Try

Code: 
RowCount = Worksheets("Egold Invoices Payment Details").Range("A" & Rows.Count).End(xlUp) + 1
 
Upvote 0
Hi,
Tried pasting that one in and i got a 'type mismatch error' Apologies for being a bit dumb (i'm a numbers girl and until yesterday morning had never done anything like this) but could you highlight exactly where i should be putting this new bit and if I should be taking anything else out.:)

Thank You in advance
Sian
 
Upvote 0
I've marked the change in red

Rich (BB code): 
Private Sub cmdcancel_Click()
Unload Me
End Sub

Private Sub cmdclear_Click()
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Private Sub cmdok_Click()

Dim RowCount As Long
Dim Control As Control

If Me.txtCRM.Value = "" Then
MsgBox "Please enter a CRM Name.", vbExclamation, "txtinitialyear"
Me.txtCRM.SetFocus
Exit Sub
End If
If Me.txtconame.Value = "" Then
MsgBox "Please enter a Company Name.", vbExclamation, "txtcontactname"
Me.txtconame.SetFocus
Exit Sub
End If
If Me.txtemail.Value = "" Then
MsgBox "Please enter an Email Address.", vbExclamation, "txtphone"
Me.txtemail.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtyearvalue.Value) Then
MsgBox "The Year Value box must contain a number.", vbExclamation, "txtdealvalue"
Me.txtyearvalue.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtdealvalue.Value) Then
MsgBox "The Deal Value box must contain a number.", vbExclamation, "chksplitpayment"
Me.txtdealvalue.SetFocus
Exit Sub
End If
'find first empty row in database'
RowCount = Worksheets("Egold Invoices Payment Details").Range("A" & Rows.Count).End(xlUp) + 1
With Worksheets("Egold Invoices").Range("A2")
.Offset(RowCount, 0).Value = Me.txtCRM.Value
.Offset(RowCount, 1).Value = Me.txtconame.Value
.Offset(RowCount, 2).Value = Me.txtcontactname.Value
.Offset(RowCount, 3).Value = Me.txtemail.Value
.Offset(RowCount, 4).Value = Me.txtphone.Value
.Offset(RowCount, 5).Value = Me.cobxegoldtype.Value
.Offset(RowCount, 15).Value = Me.txtyearvalue.Value
.Offset(RowCount, 16).Value = Me.txtdealvalue.Value
.Offset(RowCount, 17).Value = DateValue(Me.calstartdate.Value)
.Offset(RowCount, 18).Value = DateValue(Me.calenddate.Value)
.Offset(RowCount, 19).Value = Me.txtinitialyear.Value
.Offset(RowCount, 20).Value = Me.txtnotes.Value
If Me.chkgenhr.Value = True Then
.Offset(RowCount, 6).Value = "P"
Else
.Offset(RowCount, 6).Value = "X"
End If
If Me.chkukcb.Value = True Then
.Offset(RowCount, 7).Value = "P"
Else
.Offset(RowCount, 7).Value = "X"
End If
If Me.chkuktd.Value = True Then
.Offset(RowCount, 8).Value = "P"
Else
.Offset(RowCount, 8).Value = "X"
End If
If Me.chkrecres.Value = True Then
.Offset(RowCount, 9).Value = "P"
Else
.Offset(RowCount, 9).Value = "X"
End If
If Me.chkfd.Value = True Then
.Offset(RowCount, 10).Value = "P"
Else
.Offset(RowCount, 10).Value = "X"
End If
If Me.chkboard.Value = True Then
.Offset(RowCount, 11).Value = "P"
Else
.Offset(RowCount, 11).Value = "X"
End If
If Me.chkukall.Value = True Then
.Offset(RowCount, 12).Value = "P"
Else
.Offset(RowCount, 12).Value = "X"
End If
If Me.chkeurohr.Value = True Then
.Offset(RowCount, 13).Value = "P"
Else
.Offset(RowCount, 13).Value = "X"
End If
If Me.chknew.Value = True Then
.Offset(RowCount, 14).Value = "N"
Else
.Offset(RowCount, 14).Value = ""
End If
If Me.chkrenew.Value = True Then
.Offset(RowCount, 14).Value = "R"
Else
.Offset(RowCount, 14).Value = ""
End If
If Me.chkwinback.Value = True Then
.Offset(RowCount, 14).Value = "W"
Else
.Offset(RowCount, 14).Value = ""
End If
With Worksheets("Egold Invoices Payment Details").Range("A2")
If Me.chksplitpayment.Value = True Then
.Offset(RowCount, 0).Value = "Yes"
Else
.Offset(RowCount, 0).Value = "No"
End If
.Offset(RowCount, 1).Value = Me.txtsp1.Value
.Offset(RowCount, 2).Value = Me.txtsp2.Value
.Offset(RowCount, 3).Value = Me.txtsp3.Value
.Offset(RowCount, 4).Value = Me.txtsp4.Value
.Offset(RowCount, 5).Value = Me.txtsp5.Value
.Offset(RowCount, 6).Value = Me.txtsp6.Value
.Offset(RowCount, 7).Value = Me.txtsp7.Value
.Offset(RowCount, 8).Value = Me.txtsp8.Value
.Offset(RowCount, 9).Value = Me.txtsp9.Value
.Offset(RowCount, 10).Value = Me.txtsp10.Value
.Offset(RowCount, 11).Value = Me.txtsp11.Value
.Offset(RowCount, 12).Value = Me.txtsp12.Value
.Offset(RowCount, 7).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
End With
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End With
End Sub
 
Upvote 0
Hi,
Tried re-typing the code by hand and got another error code.
Error 1004: application defined or object defined error.

Sorry to be a pain!

Sian
 
Upvote 0
When you get the error click the Debug button. Which line of code is highlighted?
 
Upvote 0
the one i just put in....

RowCount = Worksheets("Egold Invoices Payment Details").Range("A" & Rows.Count).End(xlUp) + 1

Sorry to be a pain.........:confused:
 
Upvote 0
Sorry, my fault

Rich (BB code): 
RowCount = Worksheets("Egold Invoices Payment Details").Range("A" & Rows.Count).End(xlUp).Row + 1
 
Upvote 0
Hi,
Thanks for that.. I thought I was going mad ... One little thing...(she says) now it is returning data with a spare line in-between:

xxxxxxxx

xxxxxxxx

xxxxxxxx

As you can tell I am a complete novice and so glad there are people like you to help me out:)
 
Upvote 0
I'm not sure why it is doing that but you are getting the RowCount for one sheet then writing to that row in another sheet. Is that intentional?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
ListBox does not display the record I try to find
Replies
9
Views
563
Magic Polygon
M
pliskers
  • Question
Loop to create separate filtered tabs from a master data set
Replies
4
Views
733
pliskers
pliskers
M
  • Question
VBA - Returning Max value using array VBA
Replies
8
Views
938
rlv01
R
D
  • Question
VBA code to find rows that meet criteria and randomly output data from one column in corresponding row
Replies
0
Views
516
ddub25
D
I
  • Solved
(code adjustment needed) Importing data from multiple .csv files to individual column entries
Replies
2
Views
510
Itshouldbeonebutton
I

Forum statistics

Threads
1,216,109
Messages
6,128,875
Members
449,476
Latest member
pranjal9

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top