[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

CONVERT FORMULA TO rc

excel vba...2010

trying to take a formual and autopopulate down a sheet using vba..
=IF(D6="","",D6&","&E6)

concatenating   Column D and E


to similiar to this
        "=IF(RC[-4]="""","""",IF(RC[-3]="""",RC[-4],IF(RC[-2]="""",CONCATENATE(RC[-4],""-"",RC[-3]),IF(RC[-1]="""",CONCATENATE(RC[-4],""-"",RC[-3],""-"",RC[-2]),CONCATENATE(RC[-4],""-"",RC[-3],""-"",RC[-2],""-"",RC[-1])))))"


whole routine:
        Columns("F:F").Select
    Range("F6").Activate
    Selection.NumberFormat = "General"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-4]="""","""",IF(RC[-3]="""",RC[-4],IF(RC[-2]="""",CONCATENATE(RC[-4],""-"",RC[-3]),IF(RC[-1]="""",CONCATENATE(RC[-4],""-"",RC[-3],""-"",RC[-2]),CONCATENATE(RC[-4],""-"",RC[-3],""-"",RC[-2],""-"",RC[-1])))))"
    Selection.AutoFill Destination:=Range("F6:F1000"), Type:=xlFillDefault
    Range("F6:F1000").Select
    Range("B6").Select

Thanks fordraiders
0
Fordraiders
Asked:
Fordraiders
  • 4
  • 3
  • 3
1 Solution
 
NorieCommented:
What formula do you want to copy down?

The first one in the post, the one in the code or some other formula?
0
 
FordraidersAuthor Commented:
=IF(D6="","",D6&","&E6)
0
 
nutschCommented:
with rc, you rarely need to autofill

range("F6:f1000").FormulaR1C1 = _
"=IF(RC[-4]="""","""",IF(RC[-3]="""",RC[-4],IF(RC[-2]="""",CONCATENATE(RC[-4],""-"",RC[-3]),IF(RC[-1]="""",CONCATENATE(RC[-4],""-"",RC[-3],""-"",RC[-2]),CONCATENATE(RC[-4],""-"",RC[-3],""-"",RC[-2],""-"",RC[-1])))))"

range("G6:G1000").FormulaR1C1 = "=IF(RC4="""","""",RC4&"",""&RC5)"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
NorieCommented:
Why do you need to convert to R1C1 notation?

Range("F6:F1000").Formula = "=IF(D6="""", """", D6&"" ""&E6)"

Open in new window

0
 
FordraidersAuthor Commented:
dont really need to  just been using this formula for other reasons...take anything that makes it more simple
0
 
nutschCommented:
If you don't convert to R1c1 notation, every row will have the formula with D6, with R1C1, the row updates automatically.

Unless you do the autofill I guess, but IMO, not as straightforward in the coding.

Thomas
0
 
nutschCommented:
Where do you want the formula in ? F6:F1000?
range("F6:F1000").FormulaR1C1 = "=IF(RC4="""","""",RC4&"",""&RC5)" 

Open in new window


Thomas
0
 
NorieCommented:
Thomas

When I ran the code the D6 was changed as you would expect.

For example in F15:

=IF(D15="", "", D15&" "&E15)

I've never heard of there being such a difference between using A1 and R1C1 notation.
0
 
nutschCommented:
True, my bad. Learnt something new today.

T
0
 
FordraidersAuthor Commented:
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now