how to switch between R1C1 and A1

[siggisigg]

Title says it all, what is the code to switch between R1C1 coordinates, and A1?

[romperstomper]

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[siggisigg]

i cant get it to work thoughm what im trying to do is

in the first free column i want to paste som data, and further down i need to get just the 2 first letters in the row 25 lines up.
However i dont think the left function exists in vba, is there anything similar i can use?

[romperstomper]

The Left function does exist in VBA but you're trying to use it like a formula. You either need to enter a formula into the cell, or calculate the row number in VBA. Neither requires you to switch the reference style.

Please Login or Register  to view this content.

or:

Please Login or Register  to view this content.

[siggisigg]

that worked! thanks. however next step i need to check if the number in that cell is 01,04,07, or 10. Assigning Q1,Q1,Q3, and Q4 respectively.

I dont understand this very well, why wont this work ? im sure its not efficient or whatever, but i dont see why it keeps stopping

[romperstomper]

You need to double up quotes in the formula string, and you need to use commas rather than semicolons as separators (VBA wants US format):

Please Login or Register  to view this content.

[dgwebster]

for anyone who switches r1c1 and A1 style frequently I thoroughly recommend creating an adding to rebuild the formula tab that contains a toggle button to switch modes. whilst you are at it, create a button for the sheet calculation menu that calculates the selected range only. I use these two very frequently.