Solved

Excel 2010 VBA Scripting to start data on a new line

Posted on 2013-11-25
18
485 Views
Last Modified: 2013-11-27
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?
0
Comment
Question by:Rrave26
  • 9
  • 8
18 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Cells(nextrow,1).value = "This goes into column A")
0
 
LVL 12

Expert Comment

by:Harry Lee
Comment Utility
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
 

Author Comment

by:Rrave26
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Well actually if you want find the next completely empty row you can simply do

nextrow = ActiveSheet.UsedRange.Rows.Count + 1
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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
 

Author Comment

by:Rrave26
Comment Utility
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
 

Author Comment

by:Rrave26
Comment Utility
Martin,

I get a compile error at:

.cells(nextrow,1).value = "This goes into column A")
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
.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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Rrave26
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Try the attached workbook.
Q-28303657.xls
0
 

Author Comment

by:Rrave26
Comment Utility
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
 

Author Comment

by:Rrave26
Comment Utility
Here is my file for you to look at.
TEST-IM-METRICS-TRACKING.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
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
 

Author Comment

by:Rrave26
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I assume you're going to assign points here and after that you should just post the question and someone will answer.
0
 

Author Comment

by:Rrave26
Comment Utility
I sure am and already believe I did back on post  39676451.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I sure am and already believe I did back on post  39676451.
I missed that - sorry.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will show you how to use shortcut menus in the Access run-time environment.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now