Excel VBA SUM function the range is misaligned

[gsgcharles]

I am trying to do a simple summation between cell A1 and A5. I tried recording this process using the macro, however, this is what i got:

Please Login or Register  to view this content.

Naturally, i would think the range of the sum function would be:

Please Login or Register  to view this content.

as it represents cell A1 and A5. Is there any reason for this misalignment?


Thank you!

Miguel Valero

[MrShorty]

How well do you understand R1C1 notation? What are you expecting? If this formula is in C1 (R1C3), then it correctly refers (using relative references) to A1:A5. Were you expecting it to show absolute references (R1C1:R5C1 note the brackets are missing to indicate an absolute reference)?

[gsgcharles]

How well do you understand R1C1 notation? What are you expecting? If this formula is in C1 (R1C3), then it correctly refers (using relative references) to A1:A5. Were you expecting it to show absolute references (R1C1:R5C1 note the brackets are missing to indicate an absolute reference)?

Thank you for the fast reply.

Ah, I am a complete noob. I understood R1C1 as in "row 1 column 1". And you were exactly right the cell I selected was C1.

I am intending to use absolute reference for the range inside the SUM function, is there a way to do that?

P.S. What do you call these relative reference, and where can i read more on that?

[gsgcharles]

Thank you guys it worked! This is the final result:

Please Login or Register  to view this content.

[MrShorty]

Both A1 and R1C1 notation utilize relative and absolute references. It is one of the first things I learned when learning spreadsheets (long before Excel was a gleam in MS's eye). The ability to use relative and absolute references is a big part of programming spreadsheets. This help page should give you a starting place to understand the different reference styles and how to make them relative, absolute, or even mixed.

When you record the macro, make sure to enter the function using absolute references. The macro recorder should record the function reference as absolute.