Trouble with Formula in VBA

[Gr8Scott]

This enters the formula as I want it in Cell A1 of the worksheets. When complete, Cell A1 in each file gives me the NAME? error. If I double click the formula and then hit return, the formula starts working again. Is there any way to get this formula rolling within VBA and avoid the error?

Workbooks.Open Filename:= _
"\\X_DATA\RNP_FCST_ORD_PO.xls"
Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(E:E,""<>"")"
Workbooks.Open Filename:= _
"\\X_DATA\RNP_SAFETY_ONHAND.xls"
Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(E:E,""<>"")"

Many thanks,
Gr8Scott

PS - I'm adding these formulas to show the length of the data contained in each worksheet. I intend to use this length in an offset formula within my Sumproduct commands in linked files to shrink the number of rows searched per command. It should boost overall performance.

 

[Jonmo1]

Change both instances of
FormulaR1C1
to
Formula

Hope that helps.

 

[Gr8Scott]

Change both instances of
FormulaR1C1
to
Formula

Hope that helps.

Solved. Thank you!! I knew it had to be something simple, but I had no idea what was causing the problem.

 

[Jonmo1]

Glad to help, thanks for the feedback..

Just FYI,
It's not necessary to select a range to manipulate it.
Selecting tends to slow down code..
Also, making yourself depend on which cell is active is just troublesome.
Sometimes the Active Cell is not what you think it is.

You can change this
Range("A1").Select
ActiveCell.Formula = "=COUNTIF(E:E,""<>"")"

to just
Range("A1").Formula = "=COUNTIF(E:E,""<>"")"