| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Problem in macro
Hi Folks,
This seems strange to me... I have a formula which works fine... =IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) but when I try to insert that via a macro I get an error. Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" Produces: #NAME? TIA, Steve |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Problem in macro
You have:
Range("A5").FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) You probably want something like this to reference A5 =Sheet1!A5 (if in the same workbook or =[MyBook.xls]Sheet1!A5 if in another workbook HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Stephen" wrote: Hi Folks, This seems strange to me... I have a formula which works fine... =IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) but when I try to insert that via a macro I get an error. Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" Produces: #NAME? TIA, Steve |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Problem in macro
I think you need the full reference to the file. I see the file name, but
not the full path. Excel is searching for the file and can't find it. Does this work for you? ='[Executive Phone List.xls]Sheet1'!$A$1 Start the macro recorder, open the source, reference, whatever, link, and turn off the recorder. Regards, Ryan--- -- RyGuy "Stephen" wrote: Hi Folks, This seems strange to me... I have a formula which works fine... =IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) but when I try to insert that via a macro I get an error. Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" Produces: #NAME? TIA, Steve |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Problem in macro
I see what your saying but no good.
ChDir "M:\FDR\Human Resources\Phone Lists" Workbooks.Open Filename:="M:\FDR\Human Resources\Phone Lists\Management Phone List.xls" Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5="","",'M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5)" Range("B7").Select still produces: #NAME? any more thoughts? "ryguy7272" wrote: I think you need the full reference to the file. I see the file name, but not the full path. Excel is searching for the file and can't find it. Does this work for you? ='[Executive Phone List.xls]Sheet1'!$A$1 Start the macro recorder, open the source, reference, whatever, link, and turn off the recorder. Regards, Ryan--- -- RyGuy "Stephen" wrote: Hi Folks, This seems strange to me... I have a formula which works fine... =IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) but when I try to insert that via a macro I get an error. Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" Produces: #NAME? TIA, Steve |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Problem in macro
You are not referencing the sheet at all. It won't work unless the sheet is
referenced -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Stephen" wrote: I see what your saying but no good. ChDir "M:\FDR\Human Resources\Phone Lists" Workbooks.Open Filename:="M:\FDR\Human Resources\Phone Lists\Management Phone List.xls" Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5="","",'M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5)" Range("B7").Select still produces: #NAME? any more thoughts? "ryguy7272" wrote: I think you need the full reference to the file. I see the file name, but not the full path. Excel is searching for the file and can't find it. Does this work for you? ='[Executive Phone List.xls]Sheet1'!$A$1 Start the macro recorder, open the source, reference, whatever, link, and turn off the recorder. Regards, Ryan--- -- RyGuy "Stephen" wrote: Hi Folks, This seems strange to me... I have a formula which works fine... =IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) but when I try to insert that via a macro I get an error. Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" Produces: #NAME? TIA, Steve |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Problem in macro
I've tried a number of variation on your idea and each one produces a 400
error. "Barb Reinhardt" wrote: You have: Range("A5").FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) You probably want something like this to reference A5 =Sheet1!A5 (if in the same workbook or =[MyBook.xls]Sheet1!A5 if in another workbook HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Stephen" wrote: Hi Folks, This seems strange to me... I have a formula which works fine... =IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) but when I try to insert that via a macro I get an error. Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" Produces: #NAME? TIA, Steve |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Problem in macro
1. sorry but I must be missing sth.
what is the idea behind creating such a formula: "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" ? if A5 is empty then insert an empty string or else insert the value of A5? why not simply use ='M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5 ? 2. to me my prdecessors where right, file name should be in square brackets On 11 Gru, 19:45, Stephen wrote: I see what your saying but no good. ChDir "M:\FDR\Human Resources\Phone Lists" Workbooks.Open Filename:="M:\FDR\Human Resources\Phone Lists\Management Phone List.xls" Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5="","",'M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5)" Range("B7").Select still produces: #NAME? any more thoughts? "ryguy7272" wrote: I think you need the full reference to the file. *I see the file name, but not the full path. *Excel is searching for the file and can't find it.. Does this work for you? ='[Executive Phone List.xls]Sheet1'!$A$1 Start the macro recorder, open the source, reference, whatever, link, and turn off the recorder. Regards, Ryan--- -- RyGuy "Stephen" wrote: Hi Folks, This seems strange to me... I have a formula which works fine... =IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) but when I try to insert that via a macro I get an error. Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" Produces: #NAME? TIA, Steve- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Problem in macro
Barb is right
you need to point to a specific worksheet which has not been done On 11 Gru, 19:55, Barb Reinhardt wrote: You are not referencing the sheet at all. *It won't work unless the sheet is referenced -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Stephen" wrote: I see what your saying but no good. ChDir "M:\FDR\Human Resources\Phone Lists" Workbooks.Open Filename:="M:\FDR\Human Resources\Phone Lists\Management Phone List.xls" Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5="","",'M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5)" Range("B7").Select still produces: #NAME? any more thoughts? "ryguy7272" wrote: I think you need the full reference to the file. *I see the file name, but not the full path. *Excel is searching for the file and can't find it. Does this work for you? ='[Executive Phone List.xls]Sheet1'!$A$1 Start the macro recorder, open the source, reference, whatever, link, and turn off the recorder. Regards, Ryan--- -- RyGuy "Stephen" wrote: Hi Folks, This seems strange to me... I have a formula which works fine... =IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) but when I try to insert that via a macro I get an error. Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" Produces: #NAME? TIA, Steve- Ukryj cytowany tekst - - Poka¿ cytowany tekst - |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Problem in macro
Got it!
Range("A5").Select ActiveCell.Formula = "=IF('Executive Phone List.xls'!A5="""","""",'Executive Phone List.xls'!A5)" works like a charm. and the reason for the If statement is to keep blank cells blank, instead of '0'. Thanks for everyone's input, I never would have gotten here without you! "Jarek Kujawa" wrote: 1. sorry but I must be missing sth. what is the idea behind creating such a formula: "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" ? if A5 is empty then insert an empty string or else insert the value of A5? why not simply use ='M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5 ? 2. to me my prdecessors where right, file name should be in square brackets On 11 Gru, 19:45, Stephen wrote: I see what your saying but no good. ChDir "M:\FDR\Human Resources\Phone Lists" Workbooks.Open Filename:="M:\FDR\Human Resources\Phone Lists\Management Phone List.xls" Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5="","",'M:\FDR\Human Resources\Phone Lists\Executive Phone List.xls'!A5)" Range("B7").Select still produces: #NAME? any more thoughts? "ryguy7272" wrote: I think you need the full reference to the file. I see the file name, but not the full path. Excel is searching for the file and can't find it.. Does this work for you? ='[Executive Phone List.xls]Sheet1'!$A$1 Start the macro recorder, open the source, reference, whatever, link, and turn off the recorder. Regards, Ryan--- -- RyGuy "Stephen" wrote: Hi Folks, This seems strange to me... I have a formula which works fine... =IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) but when I try to insert that via a macro I get an error. Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" Produces: #NAME? TIA, Steve- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Problem in macro
Double up your double quotes, too.
Dim mystr as string mystr = "'[Executive phone list.xls]Sheet9999'!a5" 'not .formular1c1, either: range("a5").formula = "=if(" & mystr & "="""",""""," & mystr & ")" Using the variable makes it easier to type! And change the sheet name to what you need. Stephen wrote: Hi Folks, This seems strange to me... I have a formula which works fine... =IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5) but when I try to insert that via a macro I get an error. Range("A5").Select ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive Phone List.xls'!A5)" Produces: #NAME? TIA, Steve -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula Problem in Macro | Excel Discussion (Misc queries) | |||
| Excel: VB Macro programming problem with formula writing | Excel Programming | |||
| Problem with Formula n VB Macro | Excel Programming | |||
| Macro Formula Updating Problem | Excel Programming | |||
| Excel/macro Formula Problem! | Excel Programming | |||
![Stephen[_24_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
