View Full Version : Convert Formula from R1C1 to A1
YellowLabPro
08-27-2007, 07:14 AM
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
08-27-2007, 08:16 AM
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
08-27-2007, 09:18 AM
I'm going to get another screen mounted on the right to see all the code.
:bait:
YellowLabPro
08-27-2007, 09:53 AM
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
08-27-2007, 10:34 AM
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
08-27-2007, 10:41 AM
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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.