Evaluate Formula Syntax in VBA code

Hi

I have this line of code containing an Evaluate Formula:

vstrIDs = Join(Application.Transpose(Evaluate("IF(" & .Address & "<>"""",MID(" & .Address & ",2,5),"" "")")), " ")

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

MID(C174,2,FIND("^^",SUBSTITUTE(C174,"-","^^",2))-FIND("-",C174)+1)

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

One of my unsuccessful attempt:

vstrIDs = Join(Application.Transpose(Evaluate("IF(" & .Address & "<>"""",MID(" & .Address & ",2,MID(" & .Address &" ,2,FIND(""^^""",SUBSTITUTE(" & .Address & ",""-"",""^^"",2))-FIND(""-""," &.Address & ")+1)),"" "")")), " ")

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)

 'Instead of this...
  IF(" & .Address & "<>""""

 'Do this...
  IF(" & .Address & "<> & Chr$(34) & Chr$(34)

Thanks Leith,

I think I just got it with:

vstrIDs = Join(Application.Transpose(Evaluate("IF(" & .Address & "<>"""",MID(" & .Address & ",2,MID(" & .Address & ",2,FIND(""^^"",SUBSTITUTE(" & .Address & ",""-"",""^^"",2))-FIND(""-""," & .Address & ")+1)),"" "")")), " ")

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:

vstrIDs = Join(Application.Transpose(Evaluate("IF(" & .Address & "<>"""",MID(" & .Address & ",2,5),"" "")")), " ")

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

vstrIDs = Join(Application.Transpose(Evaluate("IF(" & .Address & "<>"""",MID(" & .Address & ",2,MID(" & .Address & ",2,IF(MID(" & .Address & ",9,1)=""-"",7,5))),"" "")")), " ")

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.