| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Partial File Name to Cell
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return Partial File Name to Cell
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return Partial File Name to Cell
It worked like a charm! Thanks so much!
-- 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return Partial File Name to Cell
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return Partial File Name to Cell
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" 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return Partial File Name to Cell
Worked like a charm. Just perfect.
Thanks so much! Have a wonderful weekend! -- Thanks! Dee "Vergel Adriano" wrote: 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" 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return Partial File Name to Cell
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return Partial File Name to Cell
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return Partial File Name to Cell
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return Partial File Name to Cell
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" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return Partial File Name to Cell
"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) dee wrote: 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" wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return value using partial value of a cell. | Excel Discussion (Misc queries) | |||
| return partial string | Excel Worksheet Functions | |||
| Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
| Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
| Locating a file in excel with a partial file name. | Excel Discussion (Misc queries) | |||
Linear Mode
