My VBA is changing a formula I"m trying to code in, and it's breaking my macro...

[Domroy]

I have a macro that formats a set of data. Part of it is that I this line:

ActiveCell.FormulaR1C1 = "=MID(B4,FIND(" - ",B4)+1,LEN(B4))"

The problem is, I don't want a space before and after the hyphen. When I take it out and save, it puts the spaces back in and it's driving me insane. How can I get it to just type the formula I want? With the spaces in there, it breaks the macro, and I have to debug. It used to work, but I changed the formula, then realized the new formula didn't work, so I went back to this one (which works when I test it - but only WITHOUT the spaces. HELP!

What it does is it removes the prefix on thousands of skus. So the Sku is KNT-154354. The formula finds the dash, and then gives me everything after the first dash (sometimes there are other dashes and things after the dash.

Help?

 

[Marcelo Branco]

Maybe...

ActiveCell.Formula = "=MID(B4,FIND(""-"",B4)+1,LEN(B4))"

M.

 

[Scott Huish]

Here's a different formula that will do that, and it won't break if there's no hyphen in the cell:

ActiveCell.Formula = "=REPLACE(B4,1,FIND(""-"",B4&""-""),"""")"

 

[Domroy]

YES!! Thank you, Marcelo! That fixed it.

Scott, there's always at least one hyphen, and that's where I want the formula to cut the prefix. Will your formula work for that too?

But thank you both! It's fixed!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.