Trouble with Formula in VBA

Gr8Scott

New Member
Joined
Oct 17, 2011
Messages
11
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.
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Change both instances of
FormulaR1C1
to
Formula

Hope that helps.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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,""<>"")"
 

Watch MrExcel Video

Forum statistics

Threads
1,130,329
Messages
5,641,545
Members
417,219
Latest member
FrancoisT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top