How to convert cell formula functions to code functions

[Adnan]

Hi,

Does anyone know how to convert the two following formula lines to code:

=TRIM(MID(B29,FIND("NCE:",B29,1)+5,FIND("TO",B29,1)-FIND("NCE:",B29,1)-5))
=TRIM(RIGHT(A7,LEN(A7)-FIND("TO",A7,1)-1))

I tryied it myslef but can't get it to work, I'm getting a compiling error,
here's what I have so far:

Format(Trim(Mid(InStr(1, Cells(4, 2) + 5, "NCE:", 1), InStr(1, Cells(4, 2),
"TO") - InStr(1, Cells(4, 2) - 5))), "dd-mmm-yy")
Format(Trim(Right(Cells(4, 2), Len(Cells(4, 2)) - InStr(1, "TO", Cells(4,
2), 1) - 1)), "dd-yyy-mm")

Thanks for any help provided,
Adnan

 

[ShaneDevenshire]

Hi,

easiest way is to enter them into the spreadsheet, turn on the recorder with
your cursor in the cell press F2, then Enter. Turn off the recorder and play.

Here's what I got modified a tiny bit:

ActiveCell = _

"=TRIM(MID(R[23]C[-3],FIND(""NCE:"",R[23]C[-3],1)+5,FIND(""TO"",R[23]C[-3],1)-FIND(""NCE:"",R[23]C[-3],1)-5))"
ActiveCell = _
"=TRIM(RIGHT(RC[-4],LEN(RC[-4])-FIND(""TO"",RC[-4],1)-1))"

These code lines enter the formulas in a cell. If you just want the values
look at the EVALUATE vba function.