Evaluate Formula Syntax in VBA code

Hi

I have this line of code containing an Evaluate Formula:

Please Login or Register  to view this content.

I need to replace the 5 with this formula, translated to replace C174 to .Address, as appropriate and getting the syntax right.

Please Login or Register  to view this content.

I am having trouble with all the damned double quotes...

One of my unsuccessful attempt:

Please Login or Register  to view this content.

Thanks.

Hello NBVC,

This is a longer approach but less confusing. Bypass the pairing confusion by concatenating a double quote where needed in the string using Chr$(34)

Please Login or Register  to view this content.

Thanks Leith,

I think I just got it with:

Please Login or Register  to view this content.

But I like your idea and may employ that.

Thanks again.

Ok now...

I am having still problems...

I originally had this code, which was working:

Please Login or Register  to view this content.

and I found a shorter way of replacing the 5 with a 5 or 7 based on location of a dash..

Please Login or Register  to view this content.

so instead of a definite 5, I want to say if a dash occurs at position 9, then use 7 else use 5 as my number of characters to extract in the main MID function.

For some reason this revised formula, although giving no syntax errors, is not giving any results.

Anything I am doing wrong in that second formula syntax?

I tried the Chr$(34) and got even more frustrated.

How anyone can get frustrated with Evaluate is beyond me

Q: what is the purpose of the embedded MID ?

I'm not sure I follow the logic of the formula ... ie if we consider an active range of say A1:A10 the Evaluate (in present form) can be seen in terms of:

IF($A$1:$A$10<>"",MID($A$1:$A$10,2,MID($A$1:$A$10,2,IF(MID($A$1:$A$10,9,1)="-",7,5)))," ")
tip: use debug.print with the string within the Evaluate to see what is being generated formula wise

that in red does not make much sense to me - the embedded MID by implication of the first MID is either a 5 digit or 7 digit number - meaning your first MID returns a very long string...

Hi DO,

I have a range of cells the have work order id's

Eg.

WT0903-30 M
WT1253-10 M
WU-700-50 M
WU-HT1-50 M
WU-HT1-60 M
WU-HT1-70 M
WU-HT1-80 M
WU-S0581-10 M
WU-S0581-20 M

What I am trying to extract here is, if the 9th character is a dash (-) then I want from the 2nd to the 8th characters. Otherwise, from the 2nd to the 6th character so that new list would be:

T0903
T1253
U-700
U-HT1
U-HT1
U-HT1
U-HT1
U-S0581
U-S0581
U-S0587

This is part of a much larger macro you had helped me with to extract stuff from my database.

The original formula extract only the 2nd to 6th chars... but I need to add this extra logic.

I also tried debug.print and got simply: ' ' (nothing between the single quotes). I played around with the parentheses and such, but got nowhere...

If it's easier, then I want everything from 2nd char to just before the last dash... so remove 1st char and everything after, and including, last dash.

OK that makes more sense - in which case the embedded MID isn't required (at least not as I see it)

Please Login or Register  to view this content.

DonkeyOte,

I don't know what to say.. I really did not intentionally put in that extra embedded MID as you discovered (as I am sure you would know)... I had the formula in my sheet as: =IF(C179<>"",MID(C179,2,IF(MID(C179,9,1)="-",7,5)),"") but somehow translated it wrongly in the macro... too much cut/paste, adjust...

After some testing to make sure it works it seems to work.

I had to change another spot too from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and it worked. WOW.

Thanks again