Don't understand the off-set

Hello,

I am really struggling with the off-site in VBA. I have some code and I want it to repeat a lot of times, it's simple:

=SUM(H2*'Calculation Input'!E19) and then the next H3, H4, etc.
=SUM(I2*'Calculation Input'!C19) and then the next C3, C4, etc.

However when I record a macro to get the code I get something like:

Please Login or Register  to view this content.

Basicly I do not understand the RC[-5] and R[17]C[-8]. I played around with it to change it (also the Offset number 0,1) but I do not get it. The above code should be implemented in my other code.

Please Login or Register  to view this content.

Any help is welcome with explaining this to me

Thanks!

(BTW: the sheet "pivot" and refresh data also does not work after the Macro above, however the code alone does work, can this be a timing issue? The sheet is called Pivot and the macro runs from this Pivot sheet.)

Please Login or Register  to view this content.

Basicly I do not understand the RC[-5] and R[17]C[-8].

Personally I hate the R1C1 notation - it is too hard to understand. I have never seen this notation in a recording before. You must have your options set to use the notation. I suggest you turn it off. Go to Tools\Options\General and uncheck the R1C1 reference style box.

When you re-record using the standard notation, you should find it much easier to read.

As for the offset(). it is easy when you know what it is referring to. The syntax is StartingRange.Offset(row,col).doSomething

So ActiveCell.Offset(1,0).select will select the cell immedately under the active cell.

activeCell.Offset(0,1).value=5 will enter 5 into the cell to the right of the active cell.

Range("d5").offset(1,1).NumberFormat = "m/d/yyyy" will set Date format to the cell E6

Matt

Great, it makes more sense to me, both the Offset aswell the R1C1.

So as I understand -1 goes left, 1 goes right (or -1 up and 1 down).

I will have a look to switch off the R1C1 as the offset makes it easier to read.

Thanks Mrice and Mallycat!

I have the R1C1 Style reference unchecked, but when recorded a new macro with relative references, it still gives those references.

My R1C1 is also unchecked but when recording summing a range i got

Please Login or Register  to view this content.

I would love to turn this off too!

I'm using office 2003

Regards,
Simon

There should be 2 distinct concepts here. The R1C1 formula style and the relative references - they are not the same thing.

1. To turn off R1C1, go to Tools\Options\General and deselect R1C1 reference style.

2. To turn off relative recording, you need to click the relative reference button on the Stop Macro Recording toolbar that appears when you record a macro.

Simon, your problem sounds like item 1.

Annep, If you have done this already, I would guess that yours is type 2.

Matt

Mallycat, here are some recordings i made with different criteria, they all seem to have the R1C1 format, however with the R1C1 checked the notation is visible on the worksheet, without it the worksheet formula looks like this

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regards,
Simon

OK, what I didn't know when I posted originally is that when you record the entry of a formula, the default recording is in R1C1 format.
ie ActiveCell.FormulaR1C1 = "=SUM(R[-2]C[-4]:R[5]C[-4])"

This is different to recording the selection of a cell, which has the default being A1 format

ie activecell.range("B2").select

I don't think there is a way to change this. you can always just go back into your code and change this

ActiveCell.FormulaR1C1 = "=SUM(R[-2]C[-4]:R[5]C[-4])"

to this

ActiveCell.Formula = "=SUM(b2:b50)"

Sorry mallycat i thought you had a secret way of turning off that notation in VBA

Not to worry!
Regards,
Simon