Again...insert row w/ copied formulas

[Hlatigo]

I have done a search and so far I have seen people with my exact same problem but with no real answer to my delimma. I want to insert row and copy the formula. It does copy formula but the reference stays fixed. so if one of the cells say D5+1 the copied row which moves up should say D6+1 but it says D5+1/ therefore instead of having a updated yesterday date, it has the date of two days ago and will remain fix on that date forever. Anyone have any ideas to allow the code to remain relative?

 Set lastRng = 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", -2, Format(Date, "mm-dd-yyyy")) Then
            topRng(0 + i).EntireRow.Copy '"1 + " inserts in row below
            topRng(0 + i).EntireRow.Insert
            'Paste
        End If
    Next i

this is the search i have done and nothing

http://www.mrexcel.com/board2/viewtopic.php?t=166435

http://www.mrexcel.com/board2/viewtopic.php?t=179846&highlight=insert+copy

http://www.mrexcel.com/board2/viewtopic.php?t=179518&highlight=insert+rows+formulas

 

[Hlatigo]

Ok, I have been giving this thought and I decided to make this a little more flexible. The problem I am having is populating the colum D with the date that is in the inputbox. This should solve my problem with copying the formula up. Any ideas on how to make that particular cell a value?

Thanks!!!

Private Sub cmdUpdate_Click()

    Dim cell    As Range
    Dim lastRow
    Dim lastRng As Range, topRng As Range, i As Long
    Dim C As Date
    
        

    Sheets(1).Activate
      
   C = InputBox("Please enter Date")
      
      
    Set lastRng = 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", -2, Format(Date, "mm-dd-yyyy")) Then
            topRng(0 + i).EntireRow.Copy '"1 + " inserts in row below
            topRng(0 + i).EntireRow.Insert
            topRng(0 + 0).Value = C
        End If
    Next i
        
End Sub

 

[RalphA]

Say there is a formula in A1 that you want to copy to A2, with the resulting formula being the same as the one in A1.

Here is a method, using one cell as en example, which you might make into a macro or into VBA code:
1. Copy A1 and Paste in B1 (columns increased by one)
2. Cut B1 and Paste in B2 (no change from B1)
3. Copy B1 and Paste in A2 (columns decreased by one, resulting in the identical formula still in A1)

Another method:
Just make absolute the columns or rows that you want to not change, then do a simple Copy of A1 and Paste to A2.

 

[Hlatigo]

I just figured it out....Thanks!!! here is the code just in case anyone else needs it. BTW, someone else help create the code and I just altered it a little.





Private Sub cmdUpdate_Click()

Dim cell As Range
Dim lastRow
Dim lastRng As Range, topRng As Range, i As Long
Dim C As Date



Sheets(1).Activate

C = InputBox("Please enter Date")


Set lastRng = 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 = C - 1 Then
topRng(0 + i).EntireRow.Copy '"1 + " inserts in row below
topRng(0 + i).EntireRow.Insert
topRng(i).Value = C
End If
Next i

End Sub