Selecting a cell then entire row (USMC)

[Hlatigo]

I have the following code and it looks like it should work but when I run it, it adds the rows at the very top of the selection and not inbetween yesterdays date and the forward date.

Brief explanation:
I am trying to add a row using column D as the determining factor. Column D has yesterdays date and all past dates and than it jumps to the next month and every forward month for two years or so.
11/7/2005
11/8/2005
11/9/2005
12/01/2005
01/01/2006
02/01/2006

So all i need to do is add a row in between 11/9/2005 and 12/01/2005

Any ideas? THANKS!! HAPPY MARINE CORPS BIRTHDAY!!! OOOOHRAAA!!

 

[Zack Barresse]

Hello Hlatigo,

I have the following code..

I don't see any code. Can you post what you have?

And Happy USMC B-Day!

 

[Hlatigo]

oops...im a retard


Sheets(1).Range("D65536").End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select

For Each cell In Selection
If cell.Value = DateAdd("d", -1, Format(Date, "mm-dd-yyyy")) Then

ActiveCell.Select
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
End If
Next cell

 

[Hlatigo]

bump

 

[Zack Barresse]

Okay, so month changes, insert a row inbetween them. But why then, would there not be a row inserted (as per your data in your first post) between 01/01/2006 & 02/01/2006??

 

[Hlatigo]

the month dates are montly forward rates that we are know and although they too change daily, I actually know how to code that part.

Right now, as we get in daily gas prices we slip them in inbetween yesterdays date and the most current furture month. For example, the file will say as of right now 11/13/2005 and then the next row will say 12/01/2005. Tomorrow I will need to add a row between 11/13/2005 so I can add 11/14/2005. I have to do this about 20 times on one spreadsheet so as u can see it gets old very quickly. hehehe

the code i posted selects the column but when it is about to add a row, it does it at the very top. I need to add it inbetween. ANy thoughts?

 

[Zack Barresse]

You could change these lines ..

ActiveCell.Select
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown

.. to this ..

cell.EntireRow.Insert

Also, I would change it up a bit to something like this ..

    Dim lastRng as Range topRng as Range, i as Long
    Set lastRng = .Range(.Cells(Rows.Count, "D").End(xlup)
    Set topRng = .Range(lastRng, lastRng.End(xlup))
    For i = topRng.Cells.Count to 1 Step - 1
        If topRng(i).Value = DateAdd("d", -1, Format(Date, "mm-dd-yyyy")) Then
            topRng(1 + i).EntireRow.Insert '"1 + " inserts in row below
        End If
    Next i

 

[Hlatigo]

Hi there firefytr

I tried just changing those three lines into one and it started to work but it would keep on putting lines into one row and get stuck there.

So I used your other code but I get a compile error on the following row. Not sure what it is since I have never used this method b4. any ideas? Thanks for your help so far.


Set lastRng = .Range(.Cells(Rows.Count, "D").End(xlup)

 

[Zack Barresse]

Sorry, it should be ..

Set lastRng = .Cells(Rows.Count, "D").End(xlup)

 

[Hlatigo]

ok...same line, I get a compile error again that says invalid or unqualified reference. But it only highlights the "cells"




Set lastRng = .Cells(Rows.Count, "D").End(xlUp)

sorry for keep bugging with this. thanks for ur help so far. any thoughts on how to fix. i tried changing it to cell...would i need to DIM cells?