XL_Novice
08-18-2010, 04:14 AM
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?
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?