Solved

Number each row in Excel 2007 spreadsheet using VBA

Posted on 2015-02-06
5
66 Views
Last Modified: 2016-02-10
Hi all.

I have an Excel template where the end user enters a "po number" the template then brings in the records for that po number starting in cell B8 and across to J8. Sometimes there may be 3 records pulled, other times 50, there is no way of knowing how many records are brought in.

I want to be able to number each row (starting with cell A8) that is brought in. For example, if 3 records are brought in, then I would like to put 1, 2, 3 in column A. If there are 50 records, then insert 1 - 50 in column A.

How can I do this using VBA?

Thank you in advance.
0
Comment
Question by:printmedia
  • 3
  • 2
5 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40594075
Range("A8").Activate
    ActiveCell.FormulaR1C1 = "=ROW()-7"
    LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row
    Range("A8").Select
    Selection.AutoFill Destination:=Range("A8", Cells(LastRow, 1))

Open in new window

0
 

Author Closing Comment

by:printmedia
ID: 40594097
Thanks!
0
 

Author Comment

by:printmedia
ID: 40594112
Would you mind explaining what each line of code does so I can understand it fully?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40594132
'Activate Cell A8
Range("A8").Activate

'Set the formula to Row() - 7  Starting @Number 1 in Cell A8
    ActiveCell.FormulaR1C1 = "=ROW()-7"

'Find the last row based on Column B that has data
    LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row

'Go Back to A8
    Range("A8").Select

'AutoFill the formula, from A8 to A<LastRow>
    Selection.AutoFill Destination:=Range("A8", Cells(LastRow, 1))
0
 

Author Comment

by:printmedia
ID: 40594139
Ah makes sense now, I was a little confused with the ActiveCell.FormulaR1C1. Thanks again it works great.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

743 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

11 Experts available now in Live!

Get 1:1 Help Now