Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula = problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula = problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula = problem
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 "Steven" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula = problem
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula = problem
Got it. Thank you.
"ward376" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula problem | Excel Discussion (Misc queries) | |||
Formula Problem | Excel Discussion (Misc queries) | |||
Formula problem | Excel Discussion (Misc queries) | |||
formula problem | New Users to Excel | |||
Problem with formula | Excel Discussion (Misc queries) |