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

0
votes

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"

2
vbaexcelexcel-formula
Do you need to use VBA to do this? With just this snippet, it sounds like you can use offset() in a normal formula. – Jacob Bolda
I just wrote "a" and "NumofMon" for this example with fixed values, but they can have any other value because they are actually calculated – user1040563
Yeah, offset() allows you to reference a and numofmon to dynamically create a table that you can reference. However, it sounds like you need to use VBA for this. – Jacob Bolda

2 Answers

2
votes

basically

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

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.