count cells and insert value and macro 1

[SharonMee]

Hi all:

I hope you can help with the following:

How do I write a macro that would
1) count the number of cells in column S (column 19) that has a "NO"
2) then I want excel to go to the last row and insert "Total count" in column R and the # of cells with a "NO" in column S,
Is it possible to do this with a macro?

thanks for helping

SharonMee

 

[sdraper]

The formula below might fill the bill.
=COUNTIF(S:S,"no")

Sam

 

[SkipVought]

SharonMee,

You stated that you wanted the macro to, "...go to the last row..." to insert this count.

Do you REALLY want the user to hunt for the BOTTOM of the list? Frankly, I would be annoyed if someone make me search for an important piece of information that could be placed by the designer, in a place easy for me to see at first glance. Putting Totals at the BOTTOM of reports is a vestage of paper, pencil and caluclator days. This is the Electronic Spreadsheet Age, where you can put summary information like the, right smack dab at the very TOP for the whole world to see!

But if you insist on sticking it to the user by sticking the summary at the BOTTOM...

   With ActiveSheet
     lLastRow = .Cells(.Cells.Rows.Count, "S").End(xlUp).Row + 1
     .Cells(lLastRow, "S").Formula = "=COUNTIF(S1:S" & lLastRow - 1 & ",""no"")"
   End With

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

BTW, I also failed to mention that placing a summary formula at the bottom, boxes you in. Of course, you can INSERT rows, but even THAT is fraught with the uncertainty lest the INSERT be done INCORRECTLY thereby destroying the integrity of the summary range. Such errors are more common than you think and have cost some companies millions of dollars (

http://www.eusprig.org/stories.htm)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SharonMee]

Hi Skip,

I tried the code, but nothing happened:

With ActiveSheet
lLastRow = .Cells(.Cells.Rows.Count, "S").End(xlUp).Row + 1
.Cells(lLastRow, "S").Formula = "=COUNTIF(S6:S" & lLastRow - 1 & ",""NO"")"
End With

I never thought about your suggestion, re: inserting a summary at the bottom. I agree with you. I think it's a good idea to put in at the right hand corner of the page. maybe in cell R4.

Thanks again.