Loop counting and highlighting cells that are less than

[cnash52]

I have been asked to go through an extensive excel worksheet and count which dates are before a certain day and give my supervisor a total. There are thousands of dates. I know I can have excel do this for me I just don't remember how. I would like some help with the most basic VBA commands to do this. The values are in column K and each value has two blank rows under it before the next value. If I wanted to write script to go through the column and any value that is less than 20111230 in column K put a red X in the cell in the next column how would I go about doing it? Also, if I could have a counter so that I don't then have to go through and count the X's. I was thinking making a command button then at the end of the script having a msg box show up with the total dates less than the above date. This is an small example what the dates look like. I coule really use some help on this one.

20111002


20111203


20120523


20120523


20071104

[Oredigger90]

To get a simple count, you should use the COUNTIF() function already in Excel. If you don't remember how to use it, type in a quick google search on how. But pretty much you just enter the range of data you're looking at, I'm guessing an entire column in this case; what criteria it must meet, < 20111230 i'm guessing; etc.

To Put the Red X in the Cell would be a little more tricky, but not that bad. If you posted a beginning file with a couple lines of code, as well as the result file with a few lines of code I could write one up pretty quickly.

edit:
Do you need the red x's? If not COUNTIF() should be the only thing you need, and will be WAYYY faster than running your typical macro

[Pete_UK]

Your approach sounds a bit complicated - you could just use a formula with the COUNTIFS function, and possibly conditional formatting to highlight the dates that meet your criteria.

However, before suggesting anything else to you, you should change your title to something more meaningful - check out the Forum Rules.

Pete

[cnash52]

I changed the title

[cnash52]

Okay Pete. You could actually give a productive response AND notify about post titling. If you hadn't noticed this is my first post to this thread.

[cnash52]

The problem with the countif function is that it is counting the blank cells in between the values as less than values and counting them.

[Pete_UK]

That's why I suggested COUNTIFS - with this you can specify multiple criteria, i.e. K:K,"<>", means not equal to blank, with K:K,"<20111230", so your formula will look something like:

=COUNTIFS(K:K,"<>",K:K,"<20111230")

Hope this helps.

Pete

[Oredigger90]

ok, I would use a small macro then just to calculate that number of blank rows your countif() will find. something along the lines of this

Sub returnval()
Dim j As Long
j = ActiveSheet.UsedRange.Rows.Count 'counts total number of used rows
j = ((j - 1) * 2) / 3   'if your top row has data,
                        'then two blank rows, and ends with a non blank row,
                        'this is the total number of blank
Range("A7").Value = j   'take your countif() minus this value to disregard blanks

End Sub

This way you still have a small code, but can let the countif() function do its job


edit: pete's is good lol

[Pete_UK]

Or, you could subtract from the first COUNTIF value the count of the number of blank cells:

=COUNTIF(K:K,"<20111230") - COUNTIF(K:K,"")

(compatible with XL2003 this way, if that matters).

Hope this helps.

Pete

[cnash52]

I stand corrected. I had entered the wrong date. The countif function worked perfectly and the highlighting/red x isnt really necessary. Thank you for your help.

[Pete_UK]

Well, thanks for the negative rep, posted at 3:49pm (i.e. AFTER I gave you a solution). First I've ever received !!

Whether you are new or not you should read the forum rules before posting. They are very hot on this forum about meaningful titles, and if a moderator had seen your thread they would have stopped anyone posting to it until you had changed your title.

I don't think I'll offer you anymore solutions in the future.

Pete

[Oredigger90]

Good job Pete. I need to get better with Excel's pre-programmed functions for sure.

[cnash52]

I had not received any other responses after you responded solely with the thread titling response. AFTER, I commented as to why I was unhappy with your sole response you and Oredigger's responses populated. I apologize for marring your perfect record. Truly though, thank you for your help.

[jeffreybrown]

@ cnash52,

Glad you have a working solution now and if you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save