Choosing different ranges for different formulas

Question

I have a worksheet which needs to calculate few formulas based on the data available, i have worked on two such formulas but it works fine but i assume, there could be a better way for this. I tried using Multirange but am not able to properly code the syntax.

Sub CalculateSSL()

Dim lastrow As Integer, val
Dim OutputLastRow As Long
Dim Lstrow
Lstrow = ThisWorkbook.Sheets("All Sheet-Data").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("All Sheet-Data").Activate

'the below forumla calculates the number of sales with greater than 100000

    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(RC[1]:RC[8],"">100000"")"
    Selection.AutoFill Destination:=Range("L2:L" & Lstrow)

    Range("W2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(RC[1]:RC[8],"">100000"")"
    Selection.AutoFill Destination:=Range("W2:W" & Lstrow)

    Range("AH2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(RC[1]:RC[8],"">100000"")"
    Selection.AutoFill Destination:=Range("AH2:AH" & Lstrow)

    Range("AS2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(RC[1]:RC[8],"">100000"")"
    Selection.AutoFill Destination:=Range("AS2:AS" & Lstrow)

    Range("BD2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(RC[1]:RC[8],"">100000"")"
    Selection.AutoFill Destination:=Range("BD2:BD" & Lstrow)

    Range("BO2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(RC[1]:RC[8],"">100000"")"
    Selection.AutoFill Destination:=Range("BO2:BO" & Lstrow)


'the below forumla calculates the difference between two specific items

    Range("V2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-3]"
    Range("V2").Select
    Selection.AutoFill Destination:=Range("V2:V" & Lstrow)

    Range("AG2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-3]"
    Range("AG2").Select
    Selection.AutoFill Destination:=Range("AG2:AG" & Lstrow)

    Range("AR2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-3]"
    Range("AR2").Select
    Selection.AutoFill Destination:=Range("AR2:AR" & Lstrow)

    Range("BC2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-3]"
    Range("BC2").Select
    Selection.AutoFill Destination:=Range("BC2:BC" & Lstrow)

    Range("BN2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-3]"
    Range("BN2").Select
    Selection.AutoFill Destination:=Range("BN2:BN" & Lstrow)



End Sub

@AntiDrondert Hello, yes i need a simpler way since there are many more such formulas, i just put two here.i also have a subtotal formula — VenusExcel, Mar 01 '18 at 14:41
You count last row on `Sheets("All TMS-Data")` but use it in `Worksheets("All Sheet-Data")`, is it intentional? — AntiDrondert, Mar 01 '18 at 14:44
I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Mar 01 '18 at 14:45

Harassed Dad · Answer 1 · 2018-03-01T16:48:36.837

1

Sub CalculateSSL()

Dim lastrow As Integer, val
Dim OutputLastRow As Long
Dim Lstrow
With ThisWorkbook.Sheets("All Sheet-Data")
   Lstrow = .Cells(Rows.Count, "A").End(xlUp).Row
   with .Range("L2:L" & Lstrow)
            .formula ="=COUNTIF(RC[1]:RC[8],"">100000"")"
            .copy destination:=array(.Range("W2"),.Range("AH2"),.Range("AS2"),.Range("BD2"),.Range("BO"))
   End WIth
   With .Range("v2:v" & Lstrow)
        .Formula =  "=RC[-1]-RC[-3]"
        .Copy destination:= array(.Range("BC2"),.Range("AG2"),.Range("AR2"),.range("BN2"))

   End With
End With
End Sub

EDIT Whoops - my dot references were wrong .range("BC2") is meant to expand to

ThisWorkbook.Sheets("All Sheet-Data").Range("BC2")

but it actually expanded to

ThisWorkbook.Sheets("All Sheet-Data").Range("v2:v" & Lstrow).range("BC2")

So we need to add a worksheet object to reference the sheet

Dim ws as Worksheet
Set ws = ThisWorkbook.Sheets("All Sheet-Data")

and also it's not array it's Union

 .copy destination:=union(ws.Range("W2"),ws.Range("AH2"),ws.Range("AS2"),ws.Range("BD2"),ws.Range("BO"))

edited Mar 01 '18 at 16:48

answered Mar 01 '18 at 14:56

Harassed Dad

4,669
1
10
12

Didn't know you could use multiple destinations like that when copying. – SJR Mar 01 '18 at 15:03
@Harassed Dad: Thanks much, i feel this would work even for different rows? for example the countIf happens between different columns (calculation happening horizontally), will this work for Subtotals (calculations happening Vertically?) – VenusExcel Mar 01 '18 at 15:12
Getting a Run time error 1004: Application-Defined or object Error-Defined – VenusExcel Mar 01 '18 at 15:23
.Copy Destination:=Array(.Range("W2"), .Range("AH2"), .Range("AS2"), .Range("BD2"), .Range("BO")) --- At this point – VenusExcel Mar 01 '18 at 15:24

score 1 · Accepted Answer · answered Mar 01 '18 at 14:58

Something like this?

Sub CalculateSSL()
    Dim lastrow As Integer, val
    Dim OutputLastRow As Long
    Dim Lstrow
    Dim MySheet As Worksheet, vArr(), i As Long

    Lstrow = ThisWorkbook.Sheets("All TMS-Data").Cells(Rows.Count, "A").End(xlUp).Row
    Set MySheet = ThisWorkbook.Worksheets("All Sheet-Data")

    'the below forumla calculates the number of sales with greater than 100000

    vArr = Array("L", "W", "AH", "AS", "BD", "BO")
    For i = Lbound(vArr) To Ubound(vArr)
        MySheet.Range("" & vArr(i) & "2:" & vArr(i) & Lstrow & "").FormulaR1C1 _
            = "=COUNTIF(RC[1]:RC[8],"">100000"")"
    Next i

    'the below forumla calculates the difference between two specific items

    vArr = Array("V", "AG", "AR", "BC", "BN")
    For i = Lbound(vArr) To Ubound(vArr)
        MySheet.Range("" & vArr(i) & "2:" & vArr(i) & Lstrow & "").FormulaR1C1 _
            = "=RC[-1]-RC[-3]"
    Next i
End Sub

Thanks much AntiDrondert, this work, i will try to do the same with Subtotal formulas — VenusExcel, Mar 01 '18 at 15:14

Choosing different ranges for different formulas

2 Answers2