How to convert cell formula functions to code functions
 

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

How to convert cell formula functions to code functions
 

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.

--
Thanks,
Shane Devenshire


"Adnan" wrote:

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