VBA offset inside a formula

Hi all,

I an trying to write code where when using a macro a formula will offset and look for the values on the left.

For example I have cell G7 where I have the following formula: ='[Charts_Dashboard_Masterfile.xlsx]C-PIB'!R4
And I would like a code that everytime I use it changes my formula to ='[Charts_Dashboard_Masterfile.xlsx]C-PIB'!S4 and then to ='[Charts_Dashboard_Masterfile.xlsx]C-PIB'!T4 and so on.

I have tried it using offset but it does not seem to work. Does anyone have an idea of how to do it?

Please Login or Register  to view this content.

Thank you,

Daniel

I dont quite get what you want to do and how the code you post would fit into it, but your formular1c1 line doesn't work because you are use the offset function inside quote marks. you would need something more along the lines of:

Please Login or Register  to view this content.

to untilize the offset function.

Your code does not work because what I want to offset is the value "R4C".
Like it does not necessarily means that it has to be an offset. What I basically want is to copy and paste the values from that R4C, the trick is that each time I run the macro I want it to look for the cell on the next column

Like I said, I didn't understand what you want to do or how the code you posted would achieve it, that was just an example of how to combine a function such as offset in an r1c1 reference.

To do what you want, you probably need something more like:

Please Login or Register  to view this content.

Maybe using a Helper Sheet with numbers starting at 19 for Range A1 and increasing going down.

Please Login or Register  to view this content.

Your final code may look something like this:

Please Login or Register  to view this content.