Excel VBA- use the date in a cell and insert row at the appropriate place

[sanjuboy]

Hi, I have no idea about VBA and I would be extremely thankful to anyone who could help me out with this, something I hv been breaking my head over.

Example: 1) Cell G2 has: 25-Jan-2012
2) M2 has: 9500 N2 has: 2000 O2 has:01-July-2010
3) M3: 11500 N3: 2000 O3: 01-July-2011
4) M4: 13500 N4: 2000 O4: 01-July-2012

Now, I really need a code that checks the date in G2 (which is 25-Jan-2012 in this case) and insert a row at the right place ie, between between 01-July-2011 & 01-July-2012 with the 'O' cell (inserted) autofilled with the same date as G2 so that the O column appears as a sequence - 01-July-2011 then 25-Jan-2012 (the G2 cell date) then 01-July-2012.

I am not sure if this too much to ask but it would be even more awesome if M cell inserted by vba code, comes with a formula adding the previous cells (M3+N3) with N cell inserted remaining the same as N3.

I have no idea if all this is even possible but hey the world is full of geniuses and I might even get lucky encountering one here :D

Any help greatly appreciated. Thank you.

 

[JLGWhiz]

see if this works for you

Sub t()
Dim c As Range
With ActiveSheet
    For Each c In .Range("O2", .Cells(Rows.Count, "O").End(xlUp))
        If c.Offset(1).Value > .Range("G2").Value And c.Value < .Range("G2").Value Then
            c.Offset(1).EntireRow.Insert
            c.Offset(1) = .Range("G2").Value
            Exit For
        End If
    Next
End With
End Sub

 

[JLGWhiz]

forgot the other two columns. Use this instead.

Sub t()
Dim c As Range
With ActiveSheet
    For Each c In .Range("O2", .Cells(Rows.Count, "O").End(xlUp))
        If c.Offset(1).Value > .Range("G2").Value And c.Value < .Range("G2").Value Then
            c.Offset(1).EntireRow.Insert
            c.Offset(1) = .Range("G2").Value
            .Range(c.Offset(, -2), .Cells(Rows.Count, "M").End(xlUp)).FillDown
            c.Offset(1, -1) = c.Offset(, -1).Value
            Exit For
        End If
    Next
End With
End Sub

 

[sanjuboy]

Holy cow it works. You are a genius!!!

Can the 'M' & 'N' cell inserted have a formula of it's own? Like 1) if the date in cell G2 falls between (01-Jan-any year & 30-June-any year) {including 01-Jan & 30-Jun) then 'M' cell inserted = same value as the M cell above (musn't copy the formula used in the cell above, only the value), whereas 'N' cell inserted = 2400 if the above cell is 2000, 2800 if the above cell is 2400, 4200 if the above cell is 2800. And if date in cell in G2 doesn't fall between those dates then 'M' cell inserted= uses the formula used above(not the value) and 'N' cell = 2400 if the above cell is 2000, 2800 if the above cell is 2400, 4200 if the above cell is 2800.

I know this is a lot to ask for. But if you do know and if its easy for you then I would be immensely happy if you would help me. I'll also share you the excel sheet through mail if need be.
Have attached a screenshot but the columns M=I, N=J & O=Q
Here is an edit to the original code incorporating the actual columns and it works wonderfully well. Thank you.

Sub t()Dim c As Range
With ActiveSheet
For Each c In .Range("Q9", .Cells(Rows.Count, "Q").End(xlUp))
If c.Offset(1).Value > .Range("G2").Value And c.Value < .Range("G2").Value Then
c.Offset(1).EntireRow.Insert
c.Offset(1) = .Range("G2").Value
.Range(c.Offset(, -2), .Cells(Rows.Count, "I").End(xlUp)).FillDown
c.Offset(1, -1) = c.Offset(, -1).Value
Exit For
End If
Next
End With
End Sub

Thanks again bud for the initial code. Hope you could pull this one off as well.

 

[JLGWhiz]

Holy cow it works. You are a genius!!!

Can the 'M' & 'N' cell inserted have a formula of it's own? Like 1) if the date in cell G2 falls between (01-Jan-any year & 30-June-any year) {including 01-Jan & 30-Jun) then 'M' cell inserted = same value as the M cell above (musn't copy the formula used in the cell above, only the value), whereas 'N' cell inserted = 2400 if the above cell is 2000, 2800 if the above cell is 2400, 4200 if the above cell is 2800. And if date in cell in G2 doesn't fall between those dates then 'M' cell inserted= uses the formula used above(not the value) and 'N' cell = 2400 if the above cell is 2000, 2800 if the above cell is 2400, 4200 if the above cell is 2800.

Thanks again bud for the initial code. Hope you could pull this one off as well.

That code could be written, but it would only work correctly for one application if the values are copied and pasted as values only instead of formulas. If the next execution of the code inserts a row beneath one that has values only above and should copy the formula from above and fill down, it would only copy the value and fill down. I suppose the logic to do what you are asking could be developed, but I don't want to tackle it.
Regards, JLG

 

[sanjuboy]

That code could be written, but it would only work correctly for one application if the values are copied and pasted as values only instead of formulas. If the next execution of the code inserts a row beneath one that has values only above and should copy the formula from above and fill down, it would only copy the value and fill down. I suppose the logic to do what you are asking could be developed, but I don't want to tackle it.
Regards, JLG

Ok could you make a code then with the condition - 1) if G2 has date between 01-Jan and 30-Jun incl. 01Jan&30Jun - add 2 rows below the appropriate cell ('I' & 'J'cells must be empty ie, zero. Only the first Q cell must have the same date as G2, 2nd Q cell=empty)
2) if G2 has any other date insert only one row with I, J cell blank. Q cell = G2.

Both these cases I and J cells = zero. I have a plan to manually insert the formula in these blank cells. I don't mind a little manual effort.

 

[JLGWhiz]

Ok could you make a code then with the condition - 1) if G2 has date between 01-Jan and 30-Jun incl. 01Jan&30Jun - add 2 rows below the appropriate cell ('I' & 'J'cells must be empty ie, zero. Only the first Q cell must have the same date as G2, 2nd Q cell=empty)
2) if G2 has any other date insert only one row with I, J cell blank. Q cell = G2.

Both these cases I and J cells = zero. I have a plan to manually insert the formula in these blank cells. I don't mind a little manual effort.

Maybe this will be a better way to explain the situation. When you have a column where the values of succeding cells are dependent on the preceding cell and you insert a row or rows, the integrity of the formulas in all succeding cells are corrupted and no longe valid. That was why the FillDown method was used to restore formulas in column M after a row was inserted. What you are asking now is totally different than the original post. Perhaps you should start a new thread.

 

[sanjuboy]

Maybe this will be a better way to explain the situation. When you have a column where the values of succeding cells are dependent on the preceding cell and you insert a row or rows, the integrity of the formulas in all succeding cells are corrupted and no longe valid. That was why the FillDown method was used to restore formulas in column M after a row was inserted. What you are asking now is totally different than the original post. Perhaps you should start a new thread.

Ok bud I get it now. Noobish of me not to think of how the continuity of the formula in the column gets affected when adding rows. I wish there was a way to make my excel sheet work. I did all the complex formula part and only adding a row couldn't be done using formula. But thank you for the code it was brilliant. Will see how I can use it. Thank you :D Thread can be closed.