Application-defined object error

[Rob K]

I am trying to write a simple script that just gets the value from the same cell on 52 sheets and puts them into a column, but I am really struggling, I am having difficulty accessing cell addresses using VBA, it's easy using just formulas but in VBA it seems a little more complex.

This is what I have so far:

Please Login or Register  to view this content.

This generates an application-defined object error for some reason.
What is the syntax to address this correctly.

Regards

Rob

[Norie]

This won't cause an error but I'm not sure it'll do exactly what you want.

Please Login or Register  to view this content.

[Hawkeye16]

Try this

Please Login or Register  to view this content.

Norie beat me to it... I think it was erroring because worksheets(i) is not a valid reference as a formula in excel. If you tried to type that in the cell manually it would also error out.

[Rob K]

Hmm, The R1C1 is confusing the heck out of me is there a way of doing this without the R1C1 addressing method?

[Norie]

Probably, what should the cell reference be? Is it the same for each sheet?

[Rob K]

Yes, it is I94 on every sheet.

[Hawkeye16]

The R1C1 uses relative values so each time you move down a cell in the TOTAL tab to write a new formula, the R[92] also moves down a row. If this is what you want it can also be done by using the offset function and your variable i. If you want it to be the same cell on each tab then just reference that cell instead of the R1C1 part of the formula.

Please Login or Register  to view this content.

[Norie]

All you need is this then.

Please Login or Register  to view this content.