Excel 2010 VBA Scripting to start data on a new line

Hello,  I am a newbie at VBA so please be gentile.  I've created a userform in Excel 2010 to enter straight data into a worksheet where I will then use that to create reports.  I've been able to create the script where once a record has been entered it goes to a new line:

nextrow = WorksheetFunction.CountA(Sheets("IM Raw Data").Range("A:A")) + 1

Now I'm stuck on creating the code that will start on a blank row once the spreadsheet is open.  Any ideas?
Rrave26Asked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
In response to your post ID 39676445...

With Worksheets("Sheet name goes here")
    nextrow = .UsedRange.Rows.Count + 1
    .cells(nextrow,1).value = "This goes into column A")
End With

Open in new window

0
 
Martin LissOlder than dirtCommented:
Cells(nextrow,1).value = "This goes into column A")
0
 
Harry LeeCommented:
Rrave26,

your nextrow code, can be sightly better using only VBA function instead of calling up worksheetfunction.
nextrow = Activesheet.Cells(rows.count,1).end(xlup).row +1

Open in new window

In terms of using the blank row,

You will have to first check if row 1 is empty using something like,
If Application.CountA(Range("A1").EntireRow) = 0 Then
    nextrow = 1
        Else
            nextrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1
End If

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Rrave26Author Commented:
That works the only issue I'm having now is that I currently have 4 tabs.  The first tab is a worksheet with buttons to take the user to the data entry userform.  The second tab is my raw data, the third tab is my drop down list selections for the user form and the fourth is data for one of my vlookups.  

when I enter the data into my userform the data is populated into my Raw Data Sheet but I get the "this goes into column A" phrase posted on the first tab which is my data entry userform.  I don't want that to show up.  Any suggestions?
0
 
Martin LissOlder than dirtCommented:
Well actually if you want find the next completely empty row you can simply do

nextrow = ActiveSheet.UsedRange.Rows.Count + 1
0
 
Rrave26Author Commented:
HarryHYLee,

So in your code:

nextrow = Activesheet.Cells(rows.count,1).end(xlup).row +1

does "1" after the rows.count command start counting at row 1 in the spreadsheet?  If so, and I wanted to start my data in row 2 would I just replace the 1 with a 2?
0
 
Rrave26Author Commented:
Martin,

I get a compile error at:

.cells(nextrow,1).value = "This goes into column A")
0
 
Martin LissOlder than dirtCommented:
nextrow = Activesheet.Cells(rows.count,1).end(xlup).row +1
That's not what's in my code which is nextrow = ActiveSheet.UsedRange.Rows.Count + 1

But to answer your question, the first '1' is the column. The syntax of the Cells() function is

Cells(row number, column number)

so if nextrow is for example 100,

Cells(nextrow, 1) = "blah" will put "blah" in column A of row 100
Cells(nextrow, 2) = "blah" will put "blah" in column B of row 100
Cells(nextrow +1, 5) = "blah" will put "blah" in column E of row 101
0
 
Martin LissOlder than dirtCommented:
.cells(nextrow,1).value = "This goes into column A")

has an extra ")" at the end, but in order to use .cells() rather than cells() the .cells() must be inside a With/End with block.
0
 
Rrave26Author Commented:
Martin,

first thanks for the explanation that helps.

So I put in your code:

With Worksheets("Sheet name goes here")
    nextrow = .UsedRange.Rows.Count + 1
    .cells(nextrow,1).value = "This goes into column A")
End With

so I put in ("IM Raw Data") for ("Sheet name goes here")

and when I run it I get a compile error:  Syntax error.  

What am I missing here?
0
 
Martin LissOlder than dirtCommented:
Try the attached workbook.
Q-28303657.xls
0
 
Rrave26Author Commented:
Thanks Martin,

I must have something else going on. I keep getting the same error.   I'm going to attach the file for you to look at, but I need to do it from work as there it where the file is saved right now.
0
 
Rrave26Author Commented:
Here is my file for you to look at.
TEST-IM-METRICS-TRACKING.xlsm
0
 
Martin LissOlder than dirtCommented:
Did you see my post ID ID: 39676467? In any case I corrected line 9.

Private Sub CommandButton1_Click()

Dim nextrow As Integer

nextrow = WorksheetFunction.CountA(Sheets("IM Raw Data").Range("A:A")) + 1

With Worksheets("IM Raw Data")
    nextrow = .UsedRange.Rows.Count + 1
    .Cells(nextrow,1).Value = "This goes into column A"
End With

Sheets("IM Raw Data").Cells(nextrow, 1) = Now
Sheets("IM Raw Data").Cells(nextrow, 2) = UserForm1.TextBox1.Value
Sheets("IM Raw Data").Cells(nextrow, 3) = UserForm1.TextBox2.Value
Sheets("IM Raw Data").Cells(nextrow, 4) = UserForm1.ComboBox1.Value
Sheets("IM Raw Data").Cells(nextrow, 5) = UserForm1.CheckBox1.Value
Sheets("IM Raw Data").Cells(nextrow, 6) = UserForm1.ComboBox2.Value
Sheets("IM Raw Data").Cells(nextrow, 7) = UserForm1.ComboBox3.Value
Sheets("IM Raw Data").Cells(nextrow, 8) = UserForm1.TextBox3.Value
Sheets("IM Raw Data").Cells(nextrow, 9) = UserForm1.TextBox4.Value
Sheets("IM Raw Data").Cells(nextrow, 10) = UserForm1.TextBox5.Value
Sheets("IM Raw Data").Cells(nextrow, 11) = UserForm1.TextBox6.Value
Sheets("IM Raw Data").Cells(nextrow, 12) = UserForm1.TextBox7.Value
Sheets("IM Raw Data").Cells(nextrow, 13) = UserForm1.TextBox8.Value
Sheets("IM Raw Data").Cells(nextrow, 14) = UserForm1.TextBox14.Value
Sheets("IM Raw Data").Cells(nextrow, 15) = UserForm1.TextBox15.Value
Sheets("IM Raw Data").Cells(nextrow, 16) = UserForm1.TextBox9.Value
Sheets("IM Raw Data").Cells(nextrow, 17) = UserForm1.TextBox10.Value
Sheets("IM Raw Data").Cells(nextrow, 18) = UserForm1.TextBox11.Value
Sheets("IM Raw Data").Cells(nextrow, 19) = UserForm1.TextBox12.Value
Sheets("IM Raw Data").Cells(nextrow, 20) = UserForm1.TextBox13.Value

Unload UserForm1


End Sub

Open in new window

0
 
Rrave26Author Commented:
That worked.  I did read your comment but apparently missundertood what you were saying about the ) in the code.  

Thanks for the help.  I have some additional questions on this worksheet I am working on.  would you be willing to help me with that and if so how would you like me to go about that.

Jeff
0
 
Martin LissOlder than dirtCommented:
I assume you're going to assign points here and after that you should just post the question and someone will answer.
0
 
Rrave26Author Commented:
I sure am and already believe I did back on post  39676451.
0
 
Martin LissOlder than dirtCommented:
I sure am and already believe I did back on post  39676451.
I missed that - sorry.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.