0

I want to use averageifs in one of my calculations that references one of the columns of a table called Table1 in one of the worksheets. I can do it if I paste the formula in the cells. But I have a lot of cells so I want to avoid pasting. In short I want to say:

Selection.Value = WorkSheetfunction.AverageIff(Table1["Col A"], . . . .

Where Col A is the column that contains that data values

Currently this works:

With Selection
        .FormatConditions.Add Type:=xlExpression, Formula1:=formula

where

formula = "=AVERAGEIFS(Table1[" + name + "],Table1[CELLNAME],Template!RC1,Table1[Date],Template!R1C)"

But as I said, I want to make the code more efficient and avoid pasting the formula. So how to insert a table column as a criteria into a worksheet function.

Thanks

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
medwatt
  • 103
  • 1
  • 11

1 Answers1

2

So how to insert a table column as a criteria into a worksheet function.

Use this code:

Selection.Value = WorksheetFunction.AverageIfs(Range("Table1[Col A]"), _
                                     Range("Table1[CELLNAME]"), "<criteria1>", _
                                     Range("Table1[Date]"), "<criteria2>")

UPD:

I want to populate the blanks cells with the formula
I don't want the formula to appear in the cells

In that case I suggest you following solution:

With Selection
    .FormulaR1C1 = "=AVERAGEIFS(Table1[Col A],Table1[CELLNAME],Template!RC1,Table1[Date],Template!R1C)"
    .Value = .Value
End With

it calculates values using formula and then .Value = .Value part rewrites formulas with theirs result.

And also check this link: how to avoid using Select/Active statements

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • Also if I may ask, how can I make the criteria move from cell to cell. For example if you recall from `formula = "=AVERAGEIFS(Table1[" + name + "],Table1[CELLNAME],Template!RC1,Table1[Date],Template!R1C)"` I used RC1 as a criteria meaning that the criteria changes only when the row changes ? – medwatt Apr 05 '14 at 22:43
  • To avoid a lot of ambiguous words, take a look at the photo (http://i57.tinypic.com/10gl6iw.jpg) I want to populate the blanks cells with the formula. Also I am using selection instead of a defined range just for testing purposes. – medwatt Apr 05 '14 at 23:00
  • can you explain why don't you use formula? – Dmitry Pavliv Apr 05 '14 at 23:01
  • I don't understand the question. But if you meant why I dont want to use `formula = "=AVERAGEIFS(Table1[" + name + "],Table1[CELLNAME],Template!RC1,Table1[Date],Template!R1C)"` its because I don't want the formula to appear in the cells. To avoid that I usually have to do another step. – medwatt Apr 05 '14 at 23:07
  • Thanks for your suggestion. Thats what I have been using all along and thats what I've been trying to avoid. For some reason I think this method is slower than when using worksheetfunction. – medwatt Apr 05 '14 at 23:13
  • it's not true, `worksheetfunction` would be slower. At least to incorporate worksheetfunctions in your case you need loop to paste result (because for each cell criterias are different and you need to calculate values using `worksheetfunc` _for each_ cell, and you can't do it for entire range), so it would be _much_ slower – Dmitry Pavliv Apr 05 '14 at 23:14
  • Well I was thinking there was a way just like formula in worksheet functions where you can put in cell references like RC1 and they get automatically adjusted to the correct cell. Nevertheless, thanks for all your time :)) !!! – medwatt Apr 05 '14 at 23:18