Dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
.columns(1).insert
.range("A1:A" & lastrow).FormulaR1C1 = "=IF(RC[1]<25,1,0)"
end with

And personally, I like to use excel's built in Data|subtotals whenever
possible. If I have to add a formula to put an identifier on that row, that's
ok with me, too.

Jennifer wrote:

I want to write a VB macro that will insert a column at the beginning of the
spreadsheet, add a formula that deals with another cell on the same row, and
then copy the formula for every row that has data in it. (Each row with data
will have data in the original column A). Because there may be a different
number of rows each time, I want to have the macro recognise relative
positioning (I think that's the correct term). Namely, if I can get to the
last row that has data, I want to move one cell to the right. However, when
I record the macro, it identifies a specific cell (in this case, cell A31.
Any suggestions?

Existing code:
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]<25,1,0)"
Range("A1").Select
Selection.Copy
Range("B1").Select
Selection.End(xlDown).Select
Range("A31").Activate
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("A1").Select

In addition, what I would ultimately like to do is to have the macro look at
the contents of each non-empty cell in (the original) column A to see if it
matches a specific work unit. (I have a table with all of the work units,
and have a formula which works using IF and MATCH). For each row identified
as being a work unit, I want to bold that row and add two rows above it, and
then add formulae (for totals) in the row above. Any hints how this could be
done?

--

Dave Peterson