Macro Problem R1C1 formulas

[KImba]

I have this formula:

=IF(L61>0,IF(LEFT(P61,1)="*",TEXT(L61*VLOOKUP(MID(P61,2,3),DATA!M$110:W$163,MATCH(B$4,DATA!M$110:W$110,0),FALSE)/1000,"0.0")&"kg
"&TEXT(L61*VLOOKUP(RIGHT(P61,3),DATA!M$110:W$163,MATCH(B$4,DATA!M$110:W$110,0),FALSE)/1000,"0.0")&"kg",L61*VLOOKUP(P61,DATA!M$110:W$163,MATCH(B$4,DATA!M$110:W$110,0),FALSE)/1000),"")

I want to be able to put it in any cell in column "R" with macro, but when I record myself applying the formula I dont get the whole thing eg:

ActiveCell.FormulaR1C1 = _
"=RC[-6]>0 LEFT(RC[-2],1)=""*"" TEXT(RC[-6]*VLOOKUP(MID(RC[-2],2,3),DATA!R110C[-5]:R163C[5],MATCH(R4C[-16],DATA!R110C[-5]:R110C[5],0),FALSE)/1000,""0.0"")&""kg" & Chr(10) & """ RC[-6] RIGHT(RC[-2],3)"

I can copy and paste it from 1 cell to another with VBA but cant work out how to apply it just from code????

 

[Todd Bardoni]

I have this formula:

I want to be able to put it in any cell in column "R" with macro, but when I record myself applying the formula I dont get the whole thing eg:

ActiveCell.FormulaR1C1 = _
"=RC[-6]>0 LEFT(RC[-2],1)=""*"" TEXT(RC[-6]*VLOOKUP(MID(RC[-2],2,3),DATA!R110C[-5]:R163C[5],MATCH(R4C[-16],DATA!R110C[-5]:R110C[5],0),FALSE)/1000,""0.0"")&""kg" & Chr(10) & """ RC[-6] RIGHT(RC[-2],3)"

I can copy and paste it from 1 cell to another with VBA but cant work out how to apply it just from code????

Try this:

ActiveCell.FormulaR1C1 = "=IF(R[48]C[-6]>0,IF(LEFT(R[48]C[-2],1)=""*"",TEXT(R[48]C[-6]*VLOOKUP(MID(R[48]C[-2],2,3),DATA!R110C[-5]:R163C[5],MATCH(R4C[-16],DATA!R110C[-5]:R110C[5],0),FALSE)/1000,""0.0"")&""kg""&TEXT(R[48]C[-6]*VLOOKUP(RIGHT(R[48]C[-2],3),DATA!R110C[-5]:R163C[5],MATCH(R4C[-16],DATA!R110C[-5]:R110C[5],0),FALSE)/1000,""0.0"")&""kg"",R[48]C[-6]*VLOOKUP(R[48]C[-2],DATA!R110C[-5]:R163C[5],MATCH(R4C[-16],DATA!R110C[-5]:R110C[5],0),FALSE)/1000),"""")"

BTW, all I did was go to options and selected R1C1 reference style. Then I just doubled up the quotes to make it VBA compatible. I then just simply copied and pasted into a module. :D

Hope this helps!

 

[KImba]

Thankyou very much,

That is an excelent solution to my problem.

I have many other long formulas which I only want to place in rows that need them, this will now be a simple task.

Thankyou again.