Help - input data in next empty row

sharky12345

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,404
Office Version
  1. 2016
Platform
  1. Windows
I know someone is going to come up trumps with the answer!

I have a userform where people enter data, then that data is entered into the next empty row on the worksheet.

I've worked out the code as below;

Private Sub CommandButton12_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.Cells(iRow, 3).Value = Me.txtrms1.Value
ws.Cells(iRow, 4).Value = Me.txttimeon1.Value
ws.Cells(iRow, 5).Value = Me.txttimeoff1.Value
ws.Cells(iRow, 6).Value = Me.txtcomments1.Value
ws.Cells(iRow, 3).Value = Me.txtrms2.Value
ws.Cells(iRow, 4).Value = Me.txttimeon2.Value
ws.Cells(iRow, 5).Value = Me.txttimeoff2.Value
ws.Cells(iRow, 6).Value = Me.txtcomments2.Value
ws.Cells(iRow, 3).Value = Me.txtrms3.Value
ws.Cells(iRow, 4).Value = Me.txttimeon3.Value
ws.Cells(iRow, 5).Value = Me.txttimeoff3.Value
ws.Cells(iRow, 6).Value = Me.txtcomments3.Value

Here's the problem - what I want the form to do is to enter the data from the 'txt1' boxes into the first empty row and then the data from the 'txt2' boxes into the next row, after the '1' data, and so on.

I can't work out how to do this - can anyone help?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Welcome to the Board!

To do that you'll either need to reset the last row after each value is entered or use a counter to increment off of the first one.

But by pasting in this manner you're building a largely unusable dataset (and one that won't last long), you know that right?
 
Upvote 0
Smitty,

I'm fairly new to this so not quite sure what you mean.

If it helps though the data will be cleared after 7 days and a new sheet started. I anticipate that there would be no more than about 40-50 rows of data every week, so not a huge amount.

Does this help?
 
Upvote 0
I'd imagine something like this would do it:

Code: 
Private Sub CommandButton12_Click()
    Dim iRow As Long
    Dim ws As Worksheet
        Set ws = Worksheets("Data")
        'find first empty row in database
        iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            With ws
                .Cells(iRow, "A").Value = Me.txtrms1.Value
                .Cells(iRow + 1, "A").Value = Me.txttimeon1.Value
                .Cells(iRow + 2, "A").Value = Me.txttimeoff1.Value
                .Cells(iRow + 3, "A").Value = Me.txtcomments1.Value
                .Cells(iRow + 4, "A").Value = Me.txtrms2.Value
                .Cells(iRow + 5, "A").Value = Me.txttimeon2.Value
                .Cells(iRow + 6, "A").Value = Me.txttimeoff2.Value
                .Cells(iRow + 7, "A").Value = Me.txtcomments2.Value
                .Cells(iRow + 8, "A").Value = Me.txtrms3.Value
                .Cells(iRow + 9, "A").Value = Me.txttimeon3.Value
                .Cells(iRow + 10, "A").Value = Me.txttimeoff3.Value
                .Cells(iRow + 11, "A").Value = Me.txtcomments3.Value
            End With
End Sub

Just note that when you build disparate datasets (i.e. one for each week) you lose the ability to analyze it in bulk (at least easily anyway). You're much better off building a flat-file type format where each record is in a single row and all of the data is kept together.
 
Upvote 0
Thanks Smitty - I'll give this a go and see hopw it works.

Just to clarify though, the sheet that will have the data will be cleared after 7 days and users can then start inputting on the same sheet from scratch, so no need for a seperate sheet. I've already built in the code for the administrator to email or print the completed sheet at the end of each week, before clearing the data and starting again - does this make any difference to the code I need to achieve what I want?
 
Upvote 0
Code: 
does this make any difference to the code I need to achieve what I want?

Not in the least.

I was just trying to point out that if you're gathering data anyway and if there's the remotest possibility that the data could be of use to you in the future, then it's generally a good idea to try to keep it for future analysis. And if you're going to keep it for analysis, then a flat-file or database format is the best. <!-- / message -->
 
Upvote 0
Smitty,

That doesn't work but must be my fault!

The data should be entered into the next clear row in columns C, D, E and F - what I've put in places everything in column A.

Can you help?
 
Upvote 0
The data should be entered into the next clear row in columns C, D, E and F - what I've put in places everything in column A.
Click to expand...

I'm confused, isn't that what you said you wanted it to do?

Here's the problem - what I want the form to do is to enter the data from the 'txt1' boxes into the first empty row and then the data from the 'txt2' boxes into the next row, after the '1' data, and so on.
Click to expand...

It sounds like you want the data to cascade from A1-B2-C3-D4-E5, etc? Where would the next set of data go?

Can you post an example of the results you're expecting? If you PM me your e-mail address I'll send you the board's HTML Maker which will let you post screen shots.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
VBA not finding next empty row due to formula
Replies
8
Views
967
Alex Blakenburg
A
T
  • Solved
Multi Userform Data to use next empty cell in current Row.
Replies
1
Views
420
thebadfish
T
H
  • Solved
Transpose data from columns to rows
Replies
7
Views
348
harzer
H
J
  • Question
Issues looping through excel data range when blank rows/cells are introduced.
Replies
6
Views
584
Skyybot
Skyybot
R
  • Solved
Forcing all boxes in Userform to have an entry otherwise data cannot be sent
2
Replies
13
Views
1K
mikerickson
mikerickson

Forum statistics

Threads
1,216,188
Messages
6,129,400
Members
449,508
Latest member
futureskillsacademy

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