VBA Code that insert row based on cell values

The data below is an example of the data which I have to examine to determine where to insert a row.

I need to insert a row at the start of each new financial quarter

So in the data below i need to insert a row below row 1,10,22,30

I know i need loop but I cannot get the code right to trap the correct place to insert the line

Your help would very much appreciated.

Example workbook attached

I think this is what you want:

Please Login or Register  to view this content.

Wonderful
Thanks Stnkynts very much

Hi Stnkynts
I have been examining your code and cannot understand the

iFind.EntireRow.Insert Shift:=xlDown

Which translate

Q1-2013.EntireRow.Insert Shift:=xlDown

Does the varible being a Range give the Insert the row number to insert the row?

To answer your question lets look at each part of that line of code.

iFind is a variable, declared as a range (Dim iFind as Range). So it could be any cell or combination of cells (ie B10 or B10-B20). I defined iFind by using the "Find" method, 2 lines above, so that when the loop run it will search the column top to bottom searching for the first instance of something(see What:=...).

With iFind now having its first range value, in your scenario C2, we need to tell it to select the entire row that way it doesnt just insert a new cell in that column only, hence the "EntrireRow".

"Insert" is self explanitory and Shift:=xlDown tells it in which direction to insert.

Thus you get the iFind.EntireRow.Insert Shift:=xlDown. Hope this helps.

Thanks Stnkynts for explaining,

I now trying to developing the code a bit more by entering some text on the newly inserted row and formatting the row. The inserted text uses a function to translate for example Q1 - to Janaury - March.
The problem I am having is when I step through the code the ifind variable contains "Q1-2013" so when I use the varible to send to the function it says it is the wrong type or something to that effect.

I think as you said it has no got "Q1-2013" in it but a cell reference.

Can you help me please where I pass the quarter value to the function not sure if i have amended the rest of the code correctly but i think its ok.

Thank you very much for your time.


Sub Insert_Row_byQuarter()
Dim ws As Worksheet: Set ws = Sheets("Sheet2")
Dim LastRow As Long
Dim iNum As Integer
Dim iFind As Range

LastRow = ws.Range("E" & Rows.Count).End(xlUp).row

For iNum = 1 To 4
Set iFind = ws.Range("E1:E" & LastRow).Find(What:="Q" & iNum & "*", LookIn:=xlValues, LookAt:=xlWhole)
If Not iFind Is Nothing Then
iFind.EntireRow.Insert Shift:=xlDown
iFind.Value = PeriodTitle(iFind, i + 1)
iFind.Merge
iFind.Font.Bold = True
iFind.HorizontalAlignment = xlCenter
iFind.BorderAround ColorIndex:=1, Weight:=xlThin
End If
Next iNum

End Sub

Function PeriodTitle(ByRef period As String, row As Long) As String
Select Case Left(period, 2)
Case Is = "Q1"
PeriodTitle = "January - March " & Right(period, 4)
Case Is = "Q2"
PeriodTitle = "April - June " & Right(period, 4)
Case Is = "Q3"
PeriodTitle = "July - September " & Right(period, 4)
Case Is = "Q4"
PeriodTitle = "October - December " & Right(period, 4)
Case Else
PeriodTitle = "Invalid Quarter in Row " & row + 1
End Select
End Function