This is returning the first 4 characters of the workbook name that owns the cell
you used in the formula. It doesn't matter if you use A1 or the cell that holds
the formula.

On the other hand, if you do delete column A or row 1, then the formula will
break.

Manually, you could just use the address of the cell that's getting the formula.

In code:

Option Explicit
Sub testme()

Dim myCell As Range
Set myCell = ActiveCell 'or any cell you want.

With myCell
.Formula = "=MID(CELL(""filename""," & .Address(0, 0) _
& "),SEARCH(""\["",CELL(""filename""," _
& .Address(0, 0) & "))+2,4)"
End With

End Sub

dee wrote:

Hi Dave,

Thanks very much - this worked very well. Is it possible to run this so
that it returns the invoice number in the active cell instead of one that is
referring to a specific cell?

--
Thanks!

Dee

"Dave Peterson" wrote:

If you want to keep the formula:

ActiveSheet.Range("A1").Formula _
= "=MID(CELL(""filename"",A1),SEARCH(""\["",CELL(""filename"",A1))+2,4)"

You'd want to include a cell on your worksheet formula, too:
=MID(CELL("filename",A1),SEARCH("\[",CELL("filename",A1))+2,4)



dee wrote:

Oops. I just ran it in two workbooks and it keeps referring to the name of
the last workbook in which I ran it in both workbooks.

Any suggestions?
--
Thanks!

Dee

"Vergel Adriano" wrote:

Hi Dee,

How did you do it in your VBA code? Perhaps if you post your code, others
can comment on it. But anyway, maybe try something like this:

ActiveSheet.Range("A1").Formula =
"=MID(CELL(""filename""),SEARCH(""\["",CELL(""filename""))+2,4)"

it puts your formula in cell A1 of the active sheet.

--
Hope that helps.

Vergel Adriano


"dee" wrote:

Hi,

I have the following function in my cell:

=MID(CELL("filename"),SEARCH("\[",CELL("filename"))+2,4)

This returns my invoice number, which is the first 4 characters of the file
name.

I tried to create VBA code in my Personal.xls, so that I can enter this code
into invoices more easily, but it keeps putting in the first 4 characters of
the Personal.xls file instead of the current file.

Help!

Thanks!

--
Thanks!

Dee

--

Dave Peterson


--

Dave Peterson