Convert Formula from R1C1 to A1

[YellowLabPro]

This is a formula in Excel. I used the recorder to get the quotes proper. But this put in R1C1, I would like it in A1. I attempted to replace the R1C1 references w/ A1, and the quotes, but don,t know which ones are correct or incorrect.


Cells(i, "Y").Formula = "=IF(LEFT(RC[-24],3)=""~P "",LEFT(TRIM(RC[-24]),FIND(""@@"",SUBSTITUTE(TRIM(RC[-24]),"" "",""@@"",LEN(TRIM(RC[-24]))-LEN(SUBSTITUTE(TRIM(RC[-24]),"" "",""""))))),RC[-24])"



Cells(i, "Y").Formula = "=IF(LEFT(Y" & i", 3)=""~P "",LEFT(TRIM("Y" & i),FIND(""@@"",SUBSTITUTE(TRIM("Y" & i),"" "",""@@"",LEN(TRIM("Y" & i))-LEN(SUBSTITUTE(TRIM("Y" & i),"" "",""""))))),"Y" & i)"

[Bob Phillips]

sTrim = "TRIM(A" & i & ")"
sFormula = "=IF(LEFT(A" & i & ",3)=""~P "",LEFT(" & sTrim & ",FIND(""@@"",SUBSTITUTE(" & sTrim & ","" "",""@@"",LEN(" & sTrim & ")-LEN(SUBSTITUTE(" & sTrim & ","" "",""""))))),A" & i & ")"
Cells(i, "Y").Formula = sFormula

[mdmackillop]

I'm going to get another screen mounted on the right to see all the code.
:bait:

[YellowLabPro]

MD- Ha-Ha
Don't you know they build everything over here bigger, I would be failing my countrymen if I did not keep up the high standards...

[YellowLabPro]

Bob,
I might be missing something, so if I am disregard this post and correct please.
I input the formula the way you had it originally, it was overwriting the values in col. Y w/ a zero (0), due to what I think is a circular reference. So I switched the Trim and the sFormula references to Y, and the cells formula to col. A.

sTrim = "TRIM(Y" & i & ")"
sFormula = "=IF(LEFT(Y" & i & ",3)=""~P "",LEFT(" & sTrim & ",FIND(""@@"",SUBSTITUTE(" & sTrim & ","" "",""@@"",LEN(" & sTrim & ")-LEN(SUBSTITUTE(" & sTrim & ","" "",""""))))),Y" & i & ")"
Cells(i, "A").Formula = sFormula


This finds all the cells that begin w/ ~P and removes the last two characters on the right, places this newly updated string in col. A.

If this was your intention and all we needed was a simple edit, then we are good .
I then need to copy the values in col. A to col. Y, which is easy enough to do, not to trouble you.

But if this is not your intention and I have mis-understood your instructions and you had a way to handle this in one-step I did not get it. You will need to elaborate for me.

Irregardless- how in the world you know all this stuff... :thumb

[Bob Phillips]

Doug,

As you were putting the formula in Cells(i,Y), and it was referring to RC[-24], I assumed in A1 notation you were referring to Ai.

Am I missing something?