If only i can do this =CONCATENATE("=ROT_";B2;"(A1;B1;C1;D1)")

[gone83]

Hi,

I've one question. I've created several UDF.

Please Login or Register  to view this content.

The input (FC,CT,PSM,TMS) are taken from several cells.
Say, FC = "A1", CT = "B1", PSM = "C1", TMS = "D1"

"E1" is my answer

And I also have a combox, where the user can change the function

Please Login or Register  to view this content.

And store at B2

My question is, when the user change the combox , how to change or create the UDF (based on the combox) at "E1" with maintaining the input cells (A1,B1,C1,D1)

I hope the question clear..

If only i can do this =CONCATENATE("=ROT_";B2;"(A1;B1;C1;D1)")

Merry Christmas & A Happy New Year!!!

[mikerickson]

You could write a fifth UDF and use the formula
=ROT(A1; B1; C1; D1; B2)

Please Login or Register  to view this content.

[gone83]

Thanks mikerickson.

It worked!!!

I have another question

If you notice when we typed some formula in excel, say =ROT(A1; B1; C1; D1; B2) in E1 cell.
Then, we drag A1 cell to A2, the formula in E1 change automatically to =ROT(A2; B1; C1; D1; B2)

Is it possible to do in VBA? i.e

Please Login or Register  to view this content.

them we drag R10 to R11, the code change automatically :

Please Login or Register  to view this content.

[MarvinP]

Hi gone83,

With your original question, I've seen answers where programmers create a string above the line and then let ActiveCell.Formula = String. I fumbled with it a while and couldn't get it to work a few hours ago.

For the second question about relative referencing a formula in VBA, you need to use
ActiveCell.FormulaR1C1 = .

Look at http://www.your-save-time-and-improv...e_example.html for an example or two.

[mikerickson]

The second question has to do with Excel's relative and absolute addressing.
If the formula =ROT(A1, $B$1, $C$1, $D$1, $B$2) is put into a cell, and dragged down one cell, the formula in the lower cell will have adjustded the relative reference (A1) and left the absolute references unchanged

=ROT(A2, $B$1, $C$1, $D$1, $B$2)

[gone83]

Thanks guys!!!!