Insert formula into cell, returning unexected end.

[draddy]

Hi,

I'm trying to insert this formula into my file (a VB generated file based on a currently open file) so that the new file will have a cell returning it's actual name.

What is the best way to do this? I'd like to be able to just insert a formula into a cell in the new document, but this doesn't seem to be working:

 Range("H8").Select
    ActiveCell.FormulaR1C1Local = "=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1, SEARCH("]",CELL("filename",A1))-SEARCH("[",CELL("filename",A1))-1)"

(the issue I believe is using the wrong active cell formula call)

Any tips?

Thanks,

[tigeravatar]

draddy,

If you're using VB, why insert a formula?

Range("H8").Value = ActiveWorkbook.Name

[draddy]

draddy,

If you're using VB, why insert a formula?

Range("H8").Value = ActiveWorkbook.Name

Hi Tiger,

So that the formula will be active on the final product.

to explain more, these documents are e-mailed, so every time they are opened a (1), or (2), or (1) (2) is added to the end. Since this cell is referenced in other macros, it needs to update to how the document is titled at that moment.

Thanks!

[tigeravatar]

draddy,

Alright, give this a try then:

Range("H8").Formula = "=TRIM(MID(SUBSTITUTE(CELL(""filename"",A1),""]"",REPT("" "",99)),FIND(""["",CELL(""filename"",A1))+1,99))"

[arthurbr]

Draddy,

Don't quote whole posts -- it's just clutter.

If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

[draddy]

Great, THanks tiger, that worked perfectly.

So that i'm not asking the same question multiple times, could you quickly explain what my issue was, and why this formula works when the other one didn't?

[tigeravatar]

draddy,

When using VB, if you want to put a quotation mark in a string, you need to use double quotes, so for example:

MsgBox "This is a ""test"" message for quotation marks."

Results in a message box that displays: This is a "test" message for quotation marks.


So in your original code, you have single quotation marks, which are interpreted as starting and stopping a string, instead of interprested as quotation marks. After the =MID, you should replace all the single quotes with double quotes.

I provided a different formula that's a bit more compact but provides the same result. You can see the use of double quotes in there.

[draddy]

edit: nevermind. Thanks for the help .


New problem:

Even though there are no cells linking to other workbooks, now because of this formula I added, when you open the new document with this formula, it has a link warning to "C:/ddraddy" etc. Wouldn't be good for people e-mailing it around constantly get an error.

My assumption is that it thinks this formula has a link in it because of the "[" present. Is there a way to write this formula without using the ] and ['s?