Use VBA to create and insert an indirect relative formula

[cdratz]

I imagine it is, but I can't seem to get it quite to work. I'm two days into youtube videos and forum hunting and could use some help!

I think I'm close with object variables as a range but I can't seem to get the formula to use the formula contained in another cell, much less offset a given amount from that range. I tried it as a string, but then I couldn't use offsets.

I want to:

1st) define a variable as a range (a single cell) referenced in formula(not the value) in another cell
2nd) use that range with offsets to make a FormulaR1C1 formula.

Please Login or Register  to view this content.

Say Cell A1{activecell} = A3

And I want the Formula in B1{always activecell.offset(0,1)} to = Sum(B3{always the same row number as the formula in A1, and offset 1 column to the right},B13{always activecell.offset(10,1)},B23{always activecell.offset(20.1)})

Please help!

[PCI]

cdratz,
It seems it is the first time you are with us, but there is some rules:
Code tags when posting code
Explicit Title
Can you update your thread

[Leith Ross]

Hello cdratz,

Welcome to the Forum!

I changed the title for you.

This VBA macro will place the relative R1C1 formula in the cell directly to the right of the active cell. The formula will sum the cells in the same column as the formula 2 rows down, 12 rows, and 22 rows down. Using cell A1 as the active cell the summed cells are B3, B13, and B23.

Please Login or Register  to view this content.

EDIT:
After looking at this again, I think this the macro you want to use. The first macro does not use the row number of the cell in A1. This one does.

Please Login or Register  to view this content.

[cdratz]

Thanks for the response Leith, and for the title change.

On the second version you posted, I ran into method 'range' of 'object '_global' failed error.

I see how your suggested macros are working, which will have some unrelated beneficial impact, but for this one in particular I am interested not in the absolute cell address of the active cell, I'm interested in the cell reference contained in the active cell.

In my example I said A1 was the active cell and it had the formula "=A3", it is that A3 that I would like to base all of the relative references in my sum formula on.

Maybe instead of defining the variables based on the active cell is there a way to define them based on the cell referenced in the active cell?

-Chris

[Leith Ross]

Hello cdratz,

The second macros works for me. Since you are using Excel 2010 and I am too, there should be no compatibility issues.

Do you have code elsewhere in the workbook?

EDIT:
I was able to reproduce the error by changing the A1 to "=A3". This is not a valid range reference in Excel or VBA. This will return the value of A3. Put "A3" in cell A1 and it will work.