Inserting a formula into blank rows in a varying range

[Phil Platt]

Hi all

I have a spreadsheet where the data is updated from an outside
database, and then run a macro to separate it into monthly blocks so
that it ends up looking like this:

Code Description Date Month Number

AAA Job No 1 01/02/04 2
BBB Job No 2 05/02/04 2

CCCC Job No 3 01/03/04 3

DDD Job No 4 03/04/04 4

In other words there is a blank row on each change in Month Number.

I need a macro to insert a month and year for each month into the
blank line above that month's data... so that the spreadsheet would
look like this:

Code Description Date Month Number
Feb-04
AAA Job No 1 01/02/04 2
BBB Job No 2 05/02/04 2
Mar-04
CCCC Job No 3 01/03/04 3
Apr-04
DDD Job No 4 03/04/04 4

I should point out that the number of blank rows in the whole range
will vary each time the sheet is updated, so the macro has to be able
to identify where the blank rows are each time are then insert a
formula to pick up the month and year from the next row down in the
date column. I can do the formula OK, but not the identification of
the blank rows to put it in!

I hope this explains the problem OK and I'd appreciate any help the
experts can give me.

Thanks

Phil

 

[jeff]

Hi,

Play with this.

Sub insertDate()
Dim rowindex, lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For rowindex = 2 To lastrow
Range("A" & rowindex).Select
If ActiveCell = "" Then
ActiveCell = ActiveCell.Offset(1, 5) & "-" & Year(Now
())
Selection.NumberFormat = "[$-409]mmm-yy;@"
End If
Next rowindex
End Sub


jeff

 

[Earl Kiosterud]

Phil,

It seems to me that the same macro that inserts the rows could put in the
month/year headings you want. This one is hard-coded for your locations:

Sub AddHeadings()
Dim MonthCell As Range
Set MonthCell = Range("D2")
Do
If MonthCell <> MonthCell.Offset(-1, 0) Then
MonthCell.EntireRow.Insert
MonthCell.Offset(-1, -3) = MonthName(MonthCell) & "/" &
Year(MonthCell.Offset(0, -1))
End If
Set MonthCell = MonthCell.Offset(1, 0)
MonthCell.Select
Loop While MonthCell <> ""
End Sub

 

[Phil Platt]

Jeff & Earl,

Thanks to both of you for your response - both of your suggestions
worked! But I have to say the For...Next loop worked much faster (I'm
working on a pretty slow network where these things matter!)

I'm grateful for you time and effort,

Regards

Phil