Again...insert row w/ copied formulas

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
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?

Code:
 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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
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!!!

Code:
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

Well-known Member
Joined
May 14, 2003
Messages
3,829
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

Well-known Member
Joined
Jun 3, 2002
Messages
677
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,019
Messages
5,834,984
Members
430,331
Latest member
Syed Yasir Hannan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top