CODEWITHSUNDEEP
vbaexcelexcel-formula
user1040563
user1040563

Reputation: 5261

Referring to a range of cells inside a formula in VBA Excel

I'm trying to use VBA in order to Calculate some fields. When I record a macro I get this:

ActiveCell.FormulaR1C1 = "=AVERAGEIF(R4C[-27]:R4C[-6],2012,RC[-27]:RC[-6])"

The problem is that the range for the formula can change.

I have an Integer variable NumOfMon which counts the required columns for the formula from the first column on the sheet. The formula above however, uses offsets to determine the required range but because my range can change I need to use the NumOfMon in order to use only the required cells. How can I change the formula so I can use it properly?

I tried using variables like this:

a = 28
NumofMon = 21
 ActiveCell.FormulaR1C1 = "=AVERAGEIF(R4C[-(a-1)]:R4C[-(a-NumOfMon-1)],2012,RC[-(a-1)]:RC[-(a-NumOfMon-1)])"

but I get an error: "application defined or object defined error"

Upvotes: 0

Views: 2760

Answers (2)

JosieP
JosieP

Reputation: 3410

basically

ActiveCell.FormulaR1C1 = "=AVERAGEIF(R4C[-" & (a-1) & "]:R4C[-" & (a-NumOfMon-1) & "],2012,RC[-" & (a-1) & "]:RC[-" & (a-NumOfMon-1) & "])"

Upvotes: 2

jlaverde
jlaverde

Reputation: 338

If it is possible, turn your data into a table. This way, you can refer to dynamic data (changing in size) without running into problems. For instance, to refer to column A, called 'Item' in a table called 'Table1', your reference would be '=Table1[Item]' Regardless of the rows.

Upvotes: 1

Related Questions