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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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

I
  • Question Question
Add button to increment textbox value by 1
2
Replies
13
Views
691
Artik
Artik
M
  • Solved
ListBox does not display the record I try to find
Replies
9
Views
739
Magic Polygon
M
pliskers
  • Question Question
Loop to create separate filtered tabs from a master data set
Replies
4
Views
888
pliskers
pliskers
M
  • Question Question
VBA - Returning Max value using array VBA
Replies
8
Views
2K
rlv01
R
K
  • Question Question
create fixtures but 2 vs 2
Replies
7
Views
181
kerm007
K

Forum statistics

Threads
1,224,603
Messages
6,179,856
Members
452,948
Latest member
UsmanAli786

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