CODEWITHSUNDEEP
excelvba
Chris
Chris

Reputation: 29

Copying Formula From Above

We have a macro working via a button in Excel 2013 to insert a new row with formula from above. the Problem is when it runs and i say for example copy from row 10 the formula in the new row 11 all still read back to row 10 not 11?

Sub Loop_InsertRowsandFormulas()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Risk Input Sheet")
Dim vRows As Long
Dim lastCol As Long
Dim firstRow As Long

firstRow = InputBox("Enter Row To Start Insert From.")
vRows = InputBox("Enter Number Of Rows Required")

If firstRow = 0 Or vRows = 0 Then Exit Sub
Debug.Print firstRow
IngA = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column
For myLoop = 1 To vRows
    ws.Range("A" & (firstRow + myLoop)).EntireRow.Insert
    ws.Range("A" & (firstRow + myLoop) & ":BB" & (firstRow + myLoop)).Formula = ws.Range("A" & firstRow & ":BB" & firstRow).Formula
Next

End Sub

Upvotes: 2

Views: 4634

Answers (2)

Gary's Student
Gary's Student

Reputation: 96791

You need to do a Copy/Paste. For example, if A1 contains:

=B1+C1

running:

Sub qwerty()
    Range("A2").Formula = Range("A1").Formula
End Sub

will leave A2 with =B1+C1 as well.

If you want the copied formula to "adjust" then:

Sub ytrewq()
    Range("A1").Copy Range("A2")
End Sub

EDIT#1:

Rather than:

ws.Range("A" & (firstRow + myLoop) & ":BB" & (firstRow + myLoop)).Formula = ws.Range("A" & firstRow & ":BB" & firstRow).Formula

use something like:

ws.Range("A" & firstRow & ":BB" & firstRow).Copy ws.Range("A" & (firstRow + myLoop) & ":BB" & (firstRow + myLoop))

Upvotes: 2

Chris
Chris

Reputation: 29

Cannot take any praise for this. Thanks Dave.

Sub Loop_InsertRowsandFormulas()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Risk Input Sheet")
Dim vRows As Long
Dim lastCol As Long
Dim firstRow As Long

firstRow = InputBox("Enter Row To Start Insert From.")
vRows = InputBox("Enter Number Of Rows Required")

If firstRow = 0 Or vRows = 0 Then Exit Sub
Debug.Print firstRow
IngA = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column
For Myloop = 1 To vRows
    ws.Range("A" & (firstRow + Myloop)).EntireRow.Insert
    ws.Range("N" & (firstRow) & ":AW" & (firstRow + Myloop)).FillDown
Next

End Sub

Upvotes: 1

Related Questions