Solved

# CONVERT FORMULA TO  rc

Posted on 2012-08-20
406 Views
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
Question by:fordraiders

LVL 33

Expert Comment

What formula do you want to copy down?

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

LVL 3

Author Comment

=IF(D6="","",D6&","&E6)
0

LVL 39

Expert Comment

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

LVL 33

Accepted Solution

Why do you need to convert to R1C1 notation?

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

LVL 3

Author Comment

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

LVL 39

Expert Comment

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

LVL 39

Expert Comment

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

Thomas
0

LVL 33

Expert Comment

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

LVL 39

Expert Comment

True, my bad. Learnt something new today.

T
0

LVL 3

Author Closing Comment

Thanks
0

## Featured Post

### Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.