CODEWITHSUNDEEP
excelvba
ClaireLandis
ClaireLandis

Reputation: 355

VBA Code: Average Columns to Right (Variable # of Columns)

I am writing a code that will average all values to the left in data sets that have varying numbers of columns. For example, if my data set is A1:AC1, then I used the code

ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-28]:RC[-2])"

This code works for finding the average of cells B1:AB1 which is exactly what I want in this instance, but if my next data set has say 40 columns, this code will only average the 26 cells referenced. I have researched everywhere and found that I should somehow first count the columns to the left and then use that in the average function, but I am not sure how exactly to accomplish this. How can I write a code that will average all values to the left of my active cell regardless of how many columns it is?

Upvotes: 0

Views: 64

Answers (1)

John Bustos
John Bustos

Reputation: 19544

You're close, just:

  1. Don't use relative references for your starting range.
  2. Keep the ending range relative.

Try this:

ActiveCell.FormulaR1C1 = "=AVERAGE(RC2:RC[-2])"

This will always start in column B and end 2 columns before the current cell, for example.

Hope that makes sense / helps.

Upvotes: 1

Related Questions