Solved: VBA inserts spaces into formula

[XL_Novice]

Hello.
I hope you can assist with what I thought was a simple problem.

I wish to get a part of a string after the last entry of a character in that string.

I have a formula from a book that works when I simply copy and paste the formula into the entry bar(?) but when I attempt to get a VBA module to do it, it insists on placing extra spaces in the formula and I then get a Run-Time 013 error (type mismatch).

The data string is in A1 and I want to place the formula in B1

Sample string;

c:\testdir\anydir\sample string

I want to end up with sample string in B1

The formula is below;

=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))

When I enter this formula into B1 in the usual manner, it works O.K.

However, what actually happens is that when I enter the formula in a VBA module, I get this;

Range(B1).Formula= "=MID(A1,FIND(" * ",SUBSTITUTE(A1," \ "," * ",LEN(A1)-LEN(SUBSTITUTE(A1," \ ",""))))+1,LEN(A1))"

where extra spaces are placed in quotation marks conaining a character as soon as I enter the line.

I'm using Excel 2000 on a XP Pro.

I'd appreciate any thoughts!

Sandy
P.S. I DID try to use the VBA button to enclose the code but I must be thick because I can't get it to work. I clicked the button and got [vba][\VBA]. Does the code go between the [vba]'s or do I click it again at the end of the code?

[Bob Phillips]

Try

Range(B1).Formula= "=MID(A1,FIND(""*"",SUBSTITUTE(A1,""\"",""*"",LEN(A1)-LEN(SUBSTITUTE(A1,""\"",""""))))+1,LEN(A1))"

and it goes between the [vba][\VBA]

[XL_Novice]

Try

Range(B1).Formula= "=MID(A1,FIND(""*"",SUBSTITUTE(A1,""\"",""*"",LEN(A1)-LEN(SUBSTITUTE(A1,""\"",""""))))+1,LEN(A1))"

and it goes between the [vba][\VBA]

Hi there,
thanks for the prompt reply.

I HAD already tried the Double "" entry and failed but couldn't hurt to try again.

This time, I got a Run-time error 1004 "Range of Global failed"

Sandy

[Bob Phillips]

I missed the fact that you didn't have quotes around the cell.

Range("B1").Formula= "=MID(A1,FIND(""*"",SUBSTITUTE(A1,""\"",""*"",LEN(A1)-LEN(SUBSTITUTE(A1,""\"",""""))))+1,LEN(A1))"

worked fine for me.

[XL_Novice]

I missed the fact that you didn't have quotes around the cell.

Range(B1).Formula= "=MID(A1,FIND(""*"",SUBSTITUTE(A1,""\"",""*"",LEN(A1)-LEN(SUBSTITUTE(A1,""\"",""""))))+1,LEN(A1))"

worked fine for me.
Hi again.
tried this but still got a Run-time error 1004 as per previous post.

What I don't get is that the formula worked perfectly when entered in the normal manner in the spreadsheet but fails spectacularly when attempted from VBA

Sandy

[Bob Phillips]

That last post still didn't have those quotes, it does now. As I said, it works fine here.

[XL_Novice]

Hi xld,
problem sorted.:friends:

Many thanks for your efforts on my behalf.

I presume the spaces being inserted should have given me a clue about where the problem lay. Duh!!!

Sandy