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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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,""<>"")"
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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
Back
Top