Formula = problem

[Steven]

Why does this not work in my macro?

Range("E5").Formula =
"=MID(A8,1+FIND(CHAR(1),SUBSTITUTE(A8,"\",CHAR(1),LEN(A8)-LEN(SUBSTITUTE(A8,"\","")))),255)"

It works fine when the formula it in the cell.

Thank you,

Steven

 

[ward376]

You have a missing left parentheses and an extraneous dash in there.

Try recording the entry of the formula - you'll get something like
this...

Range("e5").FormulaR1C1 = _
"=MID(R[7]C,
1+FIND(CHAR(1),SUBSTITUTE(R[7]C,""\"",CHAR(1),LEN(R[7]C)-
LEN(SUBSTITUTE(R[7]C,""\"","""")))),255)"

Cliff Edwards

 

[Rick Rothstein \(MVP - VB\)]

You have your internal quote marks messed up. Because VB uses quote marks to
delineate string constants, it interprets every single quote mark as such a
delimiter. To embed a real quote mark inside a string constant, you must
double them up. This revision to the code line you posted should do what you
want...

Range("E5").Formula =
"=MID(A8,1+FIND(CHAR(1),SUBSTITUTE(A8,""\"",CHAR(1),LEN(A8)-LEN(SUBSTITUTE(A8,""\"","""")))),255)"

Notice that I used two adjacent quote marks for each **internal** quote mark
you showed in your posted code line.

Rick

 

[ward376]

Hmmm - when I copied the formula out of your post there was a missing
left parentheses and an extra dash once pasted into a cell... weird -
but looking at it now, it looks ok. Anyway, watch the wrap in my
reply.

Cliff Edwards

 

[Steven]

Got it. Thank you.