0

I am trying to remove formulas from the excel template and include them as part of VBA code. This formula is applied to one cell for July 2019 and then it goes to same row next column Aug-2019. Once done for the current row it goes to next row for next product for July 2019 again and so on. The following is the SumIfs code. =SUMIFS('Stat Fcst Data'!J:J,'Stat Fcst Data'!$E:$E,'Stat Fcst Data $'!$E2,'Stat Fcst Data'!$F:$F,'Stat Fcst Data $'!$F2,'Stat Fcst Data'!$B:$B,'Stat Fcst Data $'!$B2)*'Stat Fcst Data $'!$D2

I have so far tried changing how I write range, criterias etc. like(cells(i11,j11),cells(i11,j11)),(cells(i11,j11),cells(i11,j11)).value Currently in my code I am using criteria variables.

Dim sumRange, criteria1, criteria2, criteria3 As Range
criteria1 = WB3.Worksheets("Stat Fcst Data").Range("E:E")
criteria2 = WB3.Worksheets("Stat Fcst Data").Range("F:F")
criteria3 = WB3.Worksheets("Stat Fcst Data").Range("B:B")
For i11 = Row51 To Row51 + NoOfRows
    For j11 = 7 To Col52
        sumRange = WB3.Worksheets("Stat Fcst Data").Range(Cells(2, j11), 
        Cells(100000, j11))
        WB3.Worksheets("Stat Fcst Data $").Range(Cells(i11, j11), 
        Cells(i11, j11)).Value = WorksheetFunction.SumIfs(sumRange _
        , criteria1, WB3.Worksheets("Stat Fcst Data $").Range(Cells(i11, 
        5), Cells(i11, 5)).Value _
        , criteria2, WB3.Worksheets("Stat Fcst Data $").Range(Cells(i11, 
        6), Cells(i11, 6)).Value _
        , criteria3, WB3.Worksheets("Stat Fcst Data $").Range(Cells(i11, 
        2), Cells(i11, 2)).Value) _
        * WB3.Worksheets("Stat Fcst Data $").Range(Cells(i11, 4), 
        Cells(i11, 4)).Value
    Next j11
Next i11

My code looks at two similar data sets called - Stat Fcst Data and Stat Fcst Data $. The code should be matching apples with apples and oranges with oranges. Currently, it runs without error but, it is not calculating sumifs and multiply that with the price which is this - WB3.Worksheets("Stat Fcst Data $").Range(Cells(i11, 4), Cells(i11, 4)).Value

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Jimmy
  • 3
  • 1
  • 6
  • Do you have `On Error Resume Next` somewhere previously in the code? Some of this looks suspect, including using `Cells` without specifying the worksheet those cells are on. Also, why the repetition? e.g. `Cells(i11, 5), Cells(i11, 5)`, and `Cells(i11, 6), Cells(i11, 6)` and so on. – BigBen Aug 20 '19 at 02:19
  • Also, in `Dim sumRange, criteria1, criteria2, criteria3 As Range`, only `criteria3` is a `Range`. You need to repeat the type: `Dim sumRange as Range, criteria1 as Range,...`. Then you need to use `Set` in the next lines: `Set criteria1 = WB3.Worksheets("Stat Fcst Data").Range("E:E")`, `Set criteria2 =...` and so on. Object variables need `Set`. Same goes for `sumRange`: `Set sumRange = ...`. – BigBen Aug 20 '19 at 02:23
  • Don't use `Range`, just `Cells`. – BigBen Aug 20 '19 at 02:39
  • Repetition as just by doing range(cells(2,6)).value probably gives me an error. I have been using in this style for some time now and this works. Any alternative would you like to suggest? For Set range.... I made that change just now still no result. – Jimmy Aug 20 '19 at 02:44
  • When you say don't use range, just cells. How can I refer to cells for 2 different sheets in a same sumifs statement? – Jimmy Aug 20 '19 at 02:46
  • `WB3.Worksheets("Stat Fcst Data").Cells(2, j11)`, for example. No `Range`. If you truly have two different cells, then use `Range` with `Cells`, but qualify the worksheet the `Cells` are on. See [this question](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) for what might otherwise happen. – BigBen Aug 20 '19 at 02:49
  • Thanks but in this particular place I am trying to convert excel formula WB3.Worksheets("Stat Fcst Data").range("K:K") for one cell and for next cell .range("L:L"). By doing just WB3.Worksheets("Stat Fcst Data").Cells(2, j11) means only cell J2 and K2 and not the whole column. – Jimmy Aug 20 '19 at 02:54
  • Yes, that's a case where you don't need `Cells` and you're fine. My no `Range` comment is to address where you currently have double instances of the same `Cells`. – BigBen Aug 20 '19 at 02:55
  • Tried again by removing range and keeping just cells(i,5). No result again. I am keeping range here as I want to consider whole column to sum Set sumRange = WB3.Worksheets("Stat Fcst Data").Range(Cells(2, j11), Cells(100000, j11)) – Jimmy Aug 20 '19 at 03:59
  • These don't look right `.Range(Cells(2, j11),Cells(100000, j11))` If you are using `cells` you need to use the right syntax.. This example, what is the range you actually want?? EG `J11:j10000` ?? – alowflyingpig Aug 20 '19 at 05:10
  • Also if you are wanting to continue on another line with the same code you need the `_` some lines are missing this and thus your code will break.. – alowflyingpig Aug 20 '19 at 05:12
  • When i pasted code here it has changed the format hence _ is not problem, my code is not failing. I need to consider .range(cells(k:k)) the whole column for one cell and then .range(cells(l:l)) - for the next cell. Till now I tried .cells( , j11) and just now tried .(cells( , j11).address, "$").(1) I am getting the column name correctly but the sumifs function is not working. There is no error at all. – Jimmy Aug 20 '19 at 05:30
  • When you have a large dataset, whether you use `Sumif` formula in the workksheet or as `WorksheetFunction.SumIfs` in a loop in VBA, your data processing will be slow. Consider reading the data in an array and handling it there. It will be much much faster than using `Sumifs` – Siddharth Rout Aug 20 '19 at 05:32

0 Answers0