Excel: Assign [COUNTIF result] a variable

[mguy27]

Ultimately I am trying to take a huge amount of data (9500-10000 rows with 6-7 colums) and do a search for text including the words "Violated Door Alarm". I then want to take the rows which contain that text in the description, copy and paste them into another workbook in Excel. I have created a program to do a simple "find, copy, paste, return, find next, copy, paste, return, etc," but I wanted to loop the program, but I need to have a finite number of loops. So, I wanted to do a COUNTIF and find out how many rows contain the words "Violated Door", and then assign that number to a variable. I then want to take that variable and loop it that many times using a Do...Until and have it stop once it has reached that number.

So, I'm looking for help with 2 things:

1) how do I assign that COUNTIF result to a variable, and
2) What will my Do Until line look like?

Any help would be awesome. thanks!

 

[SkipVought]

"I then want to take the rows which contain that text in the description, copy and paste them into another workbook"

Not a particularly good approch.

It would be ALOT simpler to use MS Query via Data > Import External Data > New Database Query... to specify the criteria and return the data in one fell swoop. All that LOOPING and COUNTING is not at all necessary!

faq68-5829


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[SkipVought]

I already suggested this as part of the solution in your other post...

...
Where AlarmType Like '%Forced Door%' 
   OR AlarmType Like '%violated door%' 
   OR AlarmType Like '%Door held open%'

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[AnotherHiggins]

May I suggest a different approach?

- Turn on your macro recorder
- Copy the sheet tab
- On the newly created sheet, do an AutoFilter
- Use a custom filter for Does Not Contain > Violated Door
- Delete all rows
- Turn off AutoFilter
- Turn off your macro recorder
- Post resulting code here for help cleaning it up and generalizing it to work for different sized data sets

You are left with only the records with match your criteria, and this will run much faster than looping through 10K records.

To answer your questions directly:

1) MyVal = Application.WorksheetFunction.CountIf(Range("A:A"), "*Violated Door*")

2) First calculate the last populated cell. There are multiple ways of doing this. See the following FAQs on this very topic:
faq707-2112
faq707-2115

Once you have the final row assigned to a variable, then use a For Next loop instead of a Do Loop. It will look something like this:

For i = 1 to LastRow
    'Do your thing there
Next i

[Edit]
I just saw Skip's reply. More elegant than my suggestion. The important thing, though, is to avoid the loop because it will run slowly.
[/Edit]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mguy27]

Skip, on tha Query, I couldn't get it to work just right, but a co-worker suggested making it simpler and doing a "find", so I was trying an alternate method.


John, that method was perfect, and yet WAY to easy. Thanks. gotta love the KISS principle.

And thank you both for helping me with this. It was still very helpful to learn for future reference. Thanks guys!

 

[SkipVought]

Of course, the first time it may take WAYYYYY too long.

But the Query Method would take all of 2 or 3 minutes to click thru the Query Wizard and Drag 'n' Drop and set criteria in the Query Grid.

Then its just a matter of Data > Refresh or Automatic Refresh, for subsequent refreshes.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[xlbo]

...and if you look at the FINDNEXT example in help, you will see that you don;t need to do a countif to stop an infinite loop - you just test the address of the 1st item that is found and loop until that address comes up again... but the filter or the qury is certainly the better way to do this ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question