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

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

# 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
• 4
• 3
• 3
1 Solution

Commented:
What formula do you want to copy down?

The first one in the post, the one in the code or some other formula?
0

Author Commented:
=IF(D6="","",D6&","&E6)
0

Commented:
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

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

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

Author Commented:
dont really need to  just been using this formula for other reasons...take anything that makes it more simple
0

Commented:
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

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

Thomas
0

Commented:
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

Commented:
True, my bad. Learnt something new today.

T
0

Author Commented:
Thanks
0

## Featured Post

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