Return Partial File Name to Cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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.
 
It worked like a charm! Thanks so much!

--
Thanks!

Dee


Vergel Adriano said:
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 said:
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!
Click to expand...
Click to expand...
 
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 said:
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 said:
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!
Click to expand...
Click to expand...
 
Hi Dee,

maybe just use a user defined function like this

Public Function InvoiceNumber() As String
If TypeName(Application.Caller) = "Range" Then
Application.Volatile
InvoiceNumber = Left(Application.Caller.Parent.Parent.Name, 4)
End If
End Function


If you keep it in the Personal workbook, you'll use it like this in other
workbooks:

=Personal.xls!InvoiceNumber()


--
Hope that helps.

Vergel Adriano


dee said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Worked like a charm. Just perfect.

Thanks so much! Have a wonderful weekend!
--
Thanks!

Dee


Vergel Adriano said:
Hi Dee,

maybe just use a user defined function like this

Public Function InvoiceNumber() As String
If TypeName(Application.Caller) = "Range" Then
Application.Volatile
InvoiceNumber = Left(Application.Caller.Parent.Parent.Name, 4)
End If
End Function


If you keep it in the Personal workbook, you'll use it like this in other
workbooks:

=Personal.xls!InvoiceNumber()


--
Hope that helps.

Vergel Adriano


dee said:
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 said:
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


:

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!
Click to expand...
Click to expand...
Click to expand...
 
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)


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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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)


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 said:
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


:

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!
Click to expand...
Click to expand...
Click to expand...
 
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
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 said:
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)


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

:

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


:

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!
Click to expand...
Click to expand...
Click to expand...
 
That worked very well. I kept trying to write code that would refer to the
active workbook and active cell, but couldn't figure it out.

Option Eplicit gave me an error, but I moved it to the very top of my
module, so hopefully this will be OK.
--
Thanks!

Dee


Dave Peterson said:
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
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 said:
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

:

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


:

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!
Click to expand...
Click to expand...
Click to expand...
 
"Option Explicit" is a directive to the compiler that you want it to force you
to declare each and every one of your variables.

You may want to read why this is important:
http://cpearson.com/excel/variables.htm
(from Chip Pearson's site)
That worked very well. I kept trying to write code that would refer to the
active workbook and active cell, but couldn't figure it out.

Option Eplicit gave me an error, but I moved it to the very top of my
module, so hopefully this will be OK.
--
Thanks!

Dee

Dave Peterson said:
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
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

:

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

:

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


:

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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top