How to convert cell formula functions to code functions


A

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
 
Ad

Advertisements

S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top