VBA - Step by Quarter

Microsoft Excel 2003 (full product)
November 17, 2009 at 16:25:22
Specs: Windows XP
I'm trying to write some VBA code to insert a new row (at the third row), then insert an incrementing quarter [1Q 2009, 2Q 2009, etc.] into cell C3, increasing upon each row insertion.

First row is a row for labels
Second rows is for maintaining row height and subsequent row formatting, both of which are different from the first row.
Third through Nth rows is for data.

In A3, I've got the following to "step" the quarters (or Years) +1 depending on the contents of the prior data point (effectively C3's data).

In C2 (my "hidden" formatting row), I've got the following to output the new quarter information - - which should ultimately copy/paste into my newly inserted C3 cell.

=IF($C$1=(RIGHT($C$3,4)+1),CONCATENATE("1Q ",$C$1),IF($C$1=1,CONCATENATE($C$1,"Q ",RIGHT($C$3,4)),IF($C$1=2,CONCATENATE($C$1,"Q ",RIGHT($C$3,4)),IF($C$1=3,CONCATENATE($C$1,"Q ",RIGHT($C$3,4)),IF(C1=4,CONCATENATE(C1,"Q ",RIGHT($C$3,4)))))))

Problem is... Every time I insert a new row, it bumps my C3 call down to C4, C5, etc. and puts the kybosh on my work.

I think that the best course of action is to write this same logical procedure in VBA, but I'm still a skiddish newbie when it comes to VBA. At least with VBA, it would always call "C3" and not increment the call, I'm thinking...

See More: VBA - Step by Quarter

Report •

November 20, 2009 at 07:38:41
Something like this?
Sub AddRow()
  Dim q As Integer, yr As Integer
  q = Trim(Left(Range("C3"), 1))
  yr = Trim(Right(Range("C3"), 4))
  yr = yr + q \ 4
  q = q Mod 4 + 1
  Range("C3") = q & "Q " & yr
End Sub

Report •

November 20, 2009 at 14:00:06
Yes, exactly! Thanks man - that works like a charm!!

Report •

Related Solutions

Ask Question