Row counter, copy master records

[Eva Shanley]

I have 2 questions related to the same spreadsheet that
has a rather odd layout. I need to insert line numbers
from 01 - 99 for all the records; I have code obtained
from this group that does that quite nicely. However, I
need preceeding zeros for lines 1 - 9 and I can't figure
out how to do that. This is the code:

Dim rowCntr As Integer, maxRowcount As Integer
rowCntr = 1
maxRowcount = 1
ActiveCell = rowCntr
Do
ActiveCell.Offset(1, 0).Select
rowCntr = rowCntr + 1
maxRowcount = maxRowcount + 1
ActiveCell = rowCntr
Loop Until rowCntr = 99 Or ActiveCell.Offset(0, 2) = ""

The layout of the file is tough as I need to bring records
from several different sheets into one sheet to transfer
to another program. The data is layed out in columns
instead of rows so there are several different formulas
that retrieve the records. When stores are
deleted/inserted, the formulas need to be copied down
again and the ranges to copy to will vary when the number
of stores changes. I want the formulas on a separate
sheet so the user can copy each formula and paste to the
transfer sheet, but because of relative referencing, the
cell references keep changing. For example, the formula
in the master sheet in A3 (references cell A1 on a data
sheet) that needs to be copied to A6 of the transfer sheet
changes to reference A4 instead of A1. I've tried using
Indirect and Offset; Indirect worked okay, but the user
can't copy the formulas down because the reference is
absolute. I'm sure this is clear as mud, but if you can
decipher it, any help would be appreciated. TIA

 

[Frank Kabel]

Hi
try
Dim rowCntr As Integer, maxRowcount As Integer
rowCntr = 1
maxRowcount = 1
ActiveCell = rowCntr
Activecell.numberformat = "00"
Do
ActiveCell.Offset(1, 0).Select
rowCntr = rowCntr + 1
maxRowcount = maxRowcount + 1
ActiveCell.value = rowCntr
activecell.numberformat = "00"
Loop Until rowCntr = 99 Or ActiveCell.Offset(0, 2) = ""

 

[Tom Ogilvy]

Dim rowCntr As Integer, maxRowcount As Integer
rowCntr = 1
maxRowcount = 1
ActiveCell = rowCntr
Do
ActiveCell.Offset(1, 0).Select
rowCntr = rowCntr + 1
maxRowcount = maxRowcount + 1
ActiveCell = rowCntr
ActiveCell.Numberformat = "00"
Loop Until rowCntr = 99 Or ActiveCell.Offset(0, 2) = ""


If you are using indirect, you can calculate the values supplied.

Rather than using 1, you can use row(A1) and when you fill, the A1 will be
changed since it is relative. You can direct it to a hidden sheet that will
not change if the sheet with the formula will have inserted cells/rows and
so forth

=indirect("Sheet1!A" & row(A1))

=Indirect("Sheet1!A" & row(HiddenSheet!A1))

as an example. You don't supply enough information for anyone to decipher
what you need, so you will have to determine how to structure your formulas.