Solved

Excel 2010 VBA Scripting to start data on a new line

Posted on 2013-11-25
18
492 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

823 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