PDA

View Full Version : Convert Formula R1C1 to Range



YellowLabPro
06-09-2007, 05:08 AM
I recorded this in the macro recorder:
Range("AF26").FormulaR1C1 = "=IF(LEN(RC[-10]=13),1,2)"

I want to change to the A1 method- this errors on "v26"
Range("af26").Formula = "=if(len(range("v26")=13),1,2)"

thanks

Bob Phillips
06-09-2007, 05:25 AM
Range("AF26").Formula = "=if(len(V2)=13,1,2)"

Bob Phillips
06-09-2007, 05:45 AM
You could use



Range("AF26").Formula = "=(LEN(V2)=13)+1"

YellowLabPro
06-09-2007, 06:04 AM
Thanks Bob,
I replaced my line w/ your last post to test, very slick.

What I am working on is a bigger project today and this is just to get my syntax setup properly. I have an existing formula in a cell, if that value is greater than 12 then I need to replace the existing formula w/ a different formula. If it is 13, use a certain formula version, 14 use another, 15 use another, and 16 use another.
I am going to go through the IF Then first on 13 to see how it will work, if performs and get the base constructed correctly, later a Select case can be an alternative in and I will try that....

But for now-
This runs in VBA but gives me a Name error in my sheet-

Range("af26").Formula = "=if(len(range(v26)=13),1,2)"

The sheet shows this in the formula bar- so I can see that range is not a function and that is why the error.... but cannot figure out how to write it back in VBE
=IF(LEN(range(V26)=13),1,2)

Bob Phillips
06-09-2007, 06:16 AM
I thought it might be just a test formula to work out the principles.

When creating a formula, you write it just as you would in Excel. Ther, you would write

=IF(LEN(V26)=13,1,2)

so you do the same in VBA



Range("AF26").Formula = "=IF(LEN(V26)=13,1,2)"


You cane even use



Range("AF26").Value= "=IF(LEN(V26)=13,1,2)"


which might make it clearer as value is just what you would type in Excel.

YellowLabPro
06-09-2007, 06:16 AM
Edited-
Removed

Bob Phillips
06-09-2007, 06:18 AM
Ok-
Got the first part-

Range("af26").Formula = "=if(len(v26=13),1,2)"

There will be more posts to follow this one- probably better to keep them here all in one place...

No, that's not right. Just re-read the previous post, so if the value in V26 is greater than 12 then just test for the value, no LEN involved.

YellowLabPro
06-09-2007, 06:38 AM
Ok...
The next step in researching this... the following line has been edited w/ several different versions. I left the most recent to show my intent.
AG26 hosts the formula that is creating the count in V26, in this case 13

What I am trying to do here is if the count is 13 in V26, then copy the formula residing in AG26 to AF26.
I am betting my approach is getting whacky...



Range("AF26").Formula = "=If(Len(v26)=13), Range(AG26).copy,2)"

YellowLabPro
06-09-2007, 06:49 AM
Ok-
I will replace the .formula w/ .value

But ultimately, and this is what I am/was trying to avoid until I had the simple things worked out was pasting the formula. It is ugly, but works....
The current formula in col AG is
=IF(ISERROR(F26&LEFT(Q26,1)&(LEFT(I26,2)&MID(I26,FIND(" ",I26)+1,2)&LEFT(J26,1)&LEFT(M26,1)&LEFT(N26,2)&LEFT(O26,2)&LEFT(R26,2)&LEFT(L26,1)&LEFT(P26,2)&LEFT(U26,1)&RIGHT(U26,4))),F26&LEFT(Q26,1)&LEFT(I26,4)&LEFT(J26,1)&LEFT(M26,1)&LEFT(N26,2)&LEFT(O26,2)&LEFT(R26,2)&LEFT(L26,1)&LEFT(P26,2)&LEFT(U26,1)&RIGHT(U26,1),F26&LEFT(Q26,1)&(LEFT(I26,2)&MID(I26,FIND(" ",I26)+1,2)&LEFT(J26,1)&LEFT(M26,1)&LEFT(N26,2)&LEFT(O26,2)&LEFT(R26,2)&LEFT(L26,1)&LEFT(P26,2)&LEFT(U26,1)&RIGHT(U26,4)))

If this produces a string that is 13 characters then I want to use a different formula instead:
It will eliminate the cell reference of Left(L,1) in all instances.

If it produces a string that is 14 characters then I have a different formula.

I have to figure out where to store the formula and where to reference it, either in the code itself or in the worksheet somewhere....

Bob Phillips
06-09-2007, 06:56 AM
Ok...
The next step in researching this... the following line has been edited w/ several different versions. I left the most recent to show my intent.
AG26 hosts the formula that is creating the count in V26, in this case 13

What I am trying to do here is if the count is 13 in V26, then copy the formula residing in AG26 to AF26.
I am betting my approach is getting whacky...



Range("AF26").Formula = "=If(Len(v26)=13), Range(AG26).copy,2)"

mis-post, see next one.

Bob Phillips
06-09-2007, 06:58 AM
Ok...
The next step in researching this... the following line has been edited w/ several different versions. I left the most recent to show my intent.
AG26 hosts the formula that is creating the count in V26, in this case 13

What I am trying to do here is if the count is 13 in V26, then copy the formula residing in AG26 to AF26.
I am betting my approach is getting whacky...



Range("AF26").Formula = "=If(Len(v26)=13), Range(AG26).copy,2)"


You need to test in VBA



Select Case Len$(Range("V26").Value)
Case 13
Range("AF26").Formula = Range("AG26").Formula
'etc.
End select

YellowLabPro
06-09-2007, 07:04 AM
Bob,
If have time before you head out can we talk about this line- if you don't can we table for when you would have some time. The Case Select is probably the best method, but I am interested in some VBA rules and logic from the following line...

Range("AF26").Formula = "=If(Len(v26)=13), Range(AG26).Copy,2)"

Bob Phillips
06-09-2007, 07:08 AM
By talk, do you mean telephony?

I have some time, as Lynne isn't back from the shops yet, and we are going off together.

YellowLabPro
06-09-2007, 07:11 AM
I was just meant back and forth here, but Malcolm showed me how to talk via the computer w/ Yahoo Instant messenger and it is free.
Whatever is best/easiest for you....

YellowLabPro
06-09-2007, 07:13 AM
One thing that might help is for me to send you my sheet. The steps are very long, so if you need to see it.....
My ability to explain things at times makes things more difficult than helps.... lol

Bob Phillips
06-09-2007, 07:14 AM
I don't use IM, I use SKype, that is also free.

Will probably crack through it quicker over a conversation rather than the forum.

YellowLabPro
06-09-2007, 07:33 AM
Sorry,
I did not get a message, been typing away waiting to get email notification.
What do I need to do to connect w/ you for skype?

Bob Phillips
06-09-2007, 07:37 AM
Just download the software and set yourself up, and tell PM me your Skype details, I will call you.

YellowLabPro
06-09-2007, 07:38 AM
ok.. give me about 5-10 minutes.
I have a computer problem at work and need to fix that real quick.

YellowLabPro
06-09-2007, 07:48 AM
Bob,
Downloading it now....

YellowLabPro
06-09-2007, 07:58 AM
Bob,
Done and sent you a PM