Solved

Excel 2010 VBA Scripting to start data on a new line

Posted on 2013-11-25
18
488 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 46

Expert Comment

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

Expert Comment

by:Harry Lee
ID: 39676436
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
ID: 39676445
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 46

Expert Comment

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

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

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39676451
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
ID: 39676458
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
ID: 39676462
Martin,

I get a compile error at:

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

Expert Comment

by:Martin Liss
ID: 39676464
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 46

Expert Comment

by:Martin Liss
ID: 39676467
.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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Rrave26
ID: 39676528
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 46

Expert Comment

by:Martin Liss
ID: 39677905
Try the attached workbook.
Q-28303657.xls
0
 

Author Comment

by:Rrave26
ID: 39679232
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
ID: 39679252
Here is my file for you to look at.
TEST-IM-METRICS-TRACKING.xlsm
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39679280
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
ID: 39679458
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 46

Expert Comment

by:Martin Liss
ID: 39679749
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
ID: 39679850
I sure am and already believe I did back on post  39676451.
0
 
LVL 46

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Outlook Free & Paid Tools
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

911 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

22 Experts available now in Live!

Get 1:1 Help Now