Problem with CountIf

[mslynng]

I have various "File Status" in Column N that shows Open, Closed, Closed w/Out Recovery. I need to get a count of Files Closed with Recovery and a % of Closed with Recovery versus Total Count. Please note that the column name will always be "File Status" but the number of rows is indeterminate.

I am a new beginner and need some help PLEASE!!!


.Cells(2, myLastColumn + 1).Formula = COUNTIF(N2:N65536,"<>Closed W/Out Recovery")

 

[SkipVought]

Hi,

Is ther a resaon that you are doing this in VBA?

Have you tried using the COUNTIF in the entire range? If so, what did you discover relative to your example?

BOTTOM LINE: unless there is a compelling reason, you ought to use the Sheet function on the sheet rather than in VBA.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mslynng]

Yes. This is a VBA project and this is just a small piece of it. It is an automated process that converts several manual steps of merging AS/400 reports in Excel. All of the other parts work. Several people will be using this on a daily basis. Thanks

 

[SkipVought]

What about the other questions?

"merging AS/400 reports in Excel"

Have you considered MS Query to import the data directly from the mainframe, rather than importing reports (YUK!)? With a QueryTable, any adjacent columns of formulas AUTOMATICALLY adjust to the returned number of rows.

Alternatively, if your "reports" are proper tabluar data, with of without one row of headings, you can use Data > Import External Data > IMPORT... with the SAME automatic adjust to formulas in adjacent columns to the right.

faq68-5829.

Can be done with or without VBA as required. But set up FIRST manually on the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}