CODEWITHSUNDEEP
vbaexcelautofill
codeLearner
codeLearner

Reputation: 86

How to autofill multiple cells with a formula using dynamic range

I have one dynamic table that is created based on the multiplication of Cells(4,"D") and Cells(6,"D") that gives the total number of rows. The column number is fixed and defined from B to G. The first row with data is line 13.

I want to write a formula deltaMassFormula that introduces in column B the following: deltaMassFormula = D13 * (G13 - F13) and automatically it fills down until the countRows equals the total number of rows (multiplication variable)

For instance, if multiplication = 4 then

E13 = D13 * (G13 - F13)

E14 = D14 * (G14 - F14)

E15 = D15 * (G15 - F15)

E16 = D16 * (G16 - F16)

My Code: 

 Dim StartCellM As Range    
 Dim lastRow As Long    
 Dim deltaMassFormula As Integer    
 Dim multiplication As Integer

 multiplication = Cells(4, "D").Value * Cells(6, "D").Value    
 countRows = multiplication - 1

 Set StartCellM = Cells(13, "E")    
 Set lastRow = Cells(13, "E") + countRows

 deltaMassFormula = Cells(13, "D") * (Cells(13, "G") - Cells(13, "F"))

 With ThisWorkbook.Sheets(1)     
    .Range("E13").Formula = deltaMassFormula    
    .Range("E13:E" & lastRow).FillDown    
 End With

Anyone can help me?

Upvotes: 0

Views: 2327

Answers (2)

tretom
tretom

Reputation: 635

it fills the column B with the formula:

Sub filler()

    Dim i As Long
    Dim startRow As Long
    Dim lastRow As Long
    Dim multiplication As Integer

    multiplication = Cells(4, 4).Value * Cells(6, 4).Value

    startRow = 13
    lastRow = startRow + multiplication - 1

    For i = startRow To lastRow
        Cells(i, 2).Formula = "=D" & i & "*(G" & i & "-F" & i & ")"
    Next i

End Sub

Upvotes: 0

tjb1
tjb1

Reputation: 757

This will fill the actual formula into the range and then replace it with the values. I also changed lastrow to long so it can be used in the other lines correctly and pulled the starting row from your range.

 Dim StartCellM As Range
 Dim lastRow As Long
 Dim multiplication As Integer

 With ThisWorkbook.Sheets(1) 
     multiplication = .Range("D4").Value * .Range("D6").Value
     countRows = multiplication - 1

     Set StartCellM = .Range("E13")
     lastRow = StartCellM.Row + countRows

    .Range("E13:E" & lastRow).Formula = "=D13*(G13-F13)"
    .Range("E13:E" & lastRow).Value = .Range("E13:E" & lastRow).Value 'Remove this line to keep formula
 End With

The formula will break if you change the row your start cell is on and don't change the row in the formula to match so this code will use the row from StartCellM in the formula instead of hard coding it.

Dim StartCellM As Range
Dim lastRow As Long, startRow As Long
Dim multiplication As Integer

With ThisWorkbook.Sheets(1)
    multiplication = .Range("D4").Value * .Range("D6").Value
    countRows = multiplication - 1

    Set StartCellM = .Range("E13")

    startRow = StartCellM.Row
    lastRow = startRow + countRows

    .Range("E" & startRow & ":E" & lastRow).Formula = "=D" & startRow & "*(G" & startRow & "-F" & startRow & ")"
    .Range("E" & startRow & ":E" & lastRow).Value = .Range("E" & startRow & ":E" & lastRow).Value 'Remove this line to keep formula
End With

I also moved everything inside the With so it will pull all the values from the correct sheet.

Upvotes: 0

Related Questions