Formula Autofill down multiple columns, using specific cells in VBA

Question

Specific cells: K4 and K8

Columns with data needed for formulas: A (values named devm), C (values named slice) and D (values named point)

Columns with formulas to go in: E, F and G (want to name these values angle, devmm and height)

Formula to go into E1: =(D1 - 1) * $K$8

Formula to go into F1: =A1 * 1000

Formula to go into G1: =(C1 - 1) * $K$4

^^^These are only with respect to Row 1

I want my macro to enter these formulas into these Row 1 cells and then autofill down to Last active Row (which I have already sorted the code out for). Relative part(s) of my code is below.

K = 1
ender = Tier * increment
last = LastTier * increment
starter = ender - (increment - 1)
If starter = 0 Then
    starter = 1
End If

sheetname1 = "Sheet1"
ActiveSheet.Name = sheetname1
ActiveSheet.Range("K2") = TankHeight
ActiveSheet.Range("K3") = LastTier - 1
ActiveSheet.Range("K4").Formula = "=$K$2/$K$3"
ActiveSheet.Range("K6").Value = 360
ActiveSheet.Range("K7") = increment
ActiveSheet.Range("K8").Formula = "=$K$6/$K$7"

' ********************************************************************
Set Range1 = Range("A1:J65536")
With Range1
    Rows(last + 2).Delete
End With

For K = starter To ender
    Devm = ActiveSheet.Range("A" & K).Value
    Rad = ActiveSheet.Range("B" & K).Value
    slice = ActiveSheet.Range("C" & K).Value
    point = ActiveSheet.Range("D" & K).Value
    ' ***Automation settings for Formulas and Autofill down to last***
    ActiveSheet.Range("E1").Formula = "=(D1-1)*$K$8"
    ActiveSheet.Range("F1").Formula = "=A1*1000"
    ActiveSheet.Range("G1").Formula = "=(C1-1)*$K$4"
    Angle = ActiveSheet.Range("E" & K).Value
    Devmm = ActiveSheet.Range("F" & K).Value
    height = ActiveSheet.Range("G" & K).Value

    K = K + 1

    ActiveSheet.Range("C1").Select

Next

-1 because your question is "I have X, I want Y" – ApplePie Sep 02 '14 at 11:07 — ApplePie, Sep 02 '14 at 11:07

score 5 · Accepted Answer · edited May 23 '17 at 10:29

5

No need for a loop. You can enter the formula in all the cells in one go

Range("E1:E" & lastRow).Formula = "=(D1 - 1) * $K$8"
Range("F1:F" & lastRow).Formula = "=A1 * 1000"
Range("G1:G" & lastRow).Formula = "=(C1 - 1) * $K$4"

where lastRow is the last row in the column.

You can find that using "Error in finding last used cell in VBA".

edited May 23 '17 at 10:29

Community

1
1

answered Sep 02 '14 at 10:57

Siddharth Rout

147,039
17
206
250

so i don't really need to the "For K = ..." part at all? There is a loop with my whole macro but that goes off whether Yes/No is clicked on a specific message box – Madeleine Thomas Sep 02 '14 at 11:04
Oh, and will the D1, A1 and so on change automatically depending on the row?? – Madeleine Thomas Sep 02 '14 at 11:05
For the formula you do not need to loop :) It will fill all the cells automatically with the correct formula till the end – Siddharth Rout Sep 02 '14 at 11:09

score 0 · Answer 2 · answered Sep 02 '14 at 10:52

There are a few ways you could approach this, but to do it with minimal changes to your code I would use the R1C1 notation for your formulas and use the reference to k for each row:

ActiveSheet.Range("E" & k).FormulaR1C1 = "=(RC[-1]-1)*R8C11"
ActiveSheet.Range("F" & k).FormulaR1C1 = "=RC[-5]*1000"
ActiveSheet.Range("G" & k).FormulaR1C1 = "=(RC[-4]-1)*R4C11"

score 0 · Answer 3 · edited Mar 18 '16 at 22:42

0

There is no need to find the last row. Here is a much simpler method:

Selection.AutoFill Destination:= _
Range(Selection, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1))

This example assumes you are one column to the right.

Double offset is possible - who knew!!

edited Mar 18 '16 at 22:42

the Tin Man

158,662
42
215
303

answered Mar 18 '16 at 22:27

Zig Paul

1

Formula Autofill down multiple columns, using specific cells in VBA

3 Answers3

Linked