R1C1 Formula Issues

Greetings All!

I have a workbook that has been working for a few years now with minor modifications. The boss has just asked for some additional information and I am hitting a brick wall in trying to modify an R1C1 formula that has been working fine.

Please Login or Register  to view this content.

Instead of RC[-1] I need it to be "LEFT(RC[-1],2)". Is this possible or do I need to re-write it as an a1 style formula?

Thanks

Instead of RC[-1] I need it to be "LEFT(RC[-1],2)"
Hum ...curious no.
RC[-1] is a cell
LEFT(RC[-1],2) is a part of the cell's value

Have you tried simply substituting "left(rc[-1],2)" in place of the "rc[-1]" portion of the formula string? I don't use the sumif function a lot, but it seems like it should be that simple.

Exactly, I had to add some information to the cell (which is a label) but the sumif criteria is looking at the entire contents of the cell instead of just the two leftmost characters in the cell. Doing it with an a1 stryle it would be left(a1,2) to return the left 2 characters in cell a1.

Jake

I think you should be fine, if you are looking for help with building the string, I came up with this and tested it. Seems to work fine.

Please Login or Register  to view this content.

Just a note about R1C1. Using R1C1 in VBA is great for loops, but it doesn't mean your sheets have to be in R1C1 format. If your sheet is A1 format your formula will just show up as A1 when the VBA is run.

Thanks! I had a stray quotation mark causing me fits.

Jake

Originally Posted by jacob@thepenpoint

Thanks! I had a stray quotation mark causing me fits.

Jake

Thanks for the rep. points. Something I do because I don't use R1C1 enough to be good at it. I write a formula in A1 in a cell and then I go to File(Excel 2010), Options, Formulas and then I check the box that has R1C1 reference style and then okay. When I go back to the sheet the formula in the cell is R1C1, I copy it, paste it into VBA and manipulate it from there.

I think I'll try that, I don't use r1c1 much either, and don't work a lot in VBA so everything tends to go stale on me. Thanks again.