Formula Problem in macro

  • Thread starter Thread starter Stephen
  • Start date Start date
S

Stephen

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
 
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.
 
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---
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
I've tried a number of variation on your idea and each one produces a 400
error.

Barb Reinhardt said:
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 said:
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
Click to expand...
Click to expand...
 
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



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 said:
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..
Click to expand...
Does this work for you?
='[Executive Phone List.xls]Sheet1'!$A$1
Click to expand...
Start the macro recorder, open the source, reference, whatever, link, and
turn off the recorder.
Click to expand...

- Poka¿ cytowany tekst -
Click to expand...
 
Barb is right
you need to point to a specific worksheet which has not been done

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 said:
I see what your saying but no good.
Click to expand...
ChDir "M:\FDR\Human Resources\Phone Lists"
Workbooks.Open Filename:="M:\FDR\Human Resources\Phone Lists\Management
Phone List.xls"
Click to expand...
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
Click to expand...
still produces:

any more thoughts?
Click to expand...
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
:
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 -
Click to expand...
Click to expand...

- Poka¿ cytowany tekst -
Click to expand...
 
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 said:
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



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 said:
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..
Click to expand...
Does this work for you?
='[Executive Phone List.xls]Sheet1'!$A$1
Click to expand...
Start the macro recorder, open the source, reference, whatever, link, and
turn off the recorder.

"Stephen" wrote:
Click to expand...
Hi Folks,
Click to expand...
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.
Click to expand...
Range("A5").Select
ActiveCell.FormulaR1C1 = "=IF('Executive Phone List.xls'!A5="","",'Executive
Phone List.xls'!A5)"



Steve- Ukryj cytowany tekst -
Click to expand...

- Pokaż cytowany tekst -
Click to expand...
Click to expand...
 
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.
 
Back
Top