Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel 2010 VBA Scripting to start data on a new line

Posted on 2013-11-25
18
Medium Priority
?
529 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 50

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 50

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 50

Accepted Solution

by:
Martin Liss earned 2000 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 50

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 50

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
 

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 50

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 50

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 50

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 50

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

580 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