Optimizing WorksheetFunction.CountIf

**daffodil11** · 02-25-2014, 03:45 PM

Hellos!

I'm currently using WorksheetFunction.CountIf to determine if each cell in a range has a matching value in another range.

In my limited VB knowledge, I went with this function because I am unfamiliar with any other options. In terms of processing, would there be a smarter solution?

(I'll post a workbook sample as soon as the current macro completes; running on 12 minutes so far.)

The code I've written so far:

Please Login or Register  to view this content.

It's just writing the word Exclude out in column AR when G matches a record on my Exclusions list. Is there a smart way to write this?

**Solus Rankin** · 02-25-2014, 04:23 PM

Does it have to be a macro? Sounds like a vlookup might suit.

at least I don't think it would take 12+ minutes.

**daffodil11** · 02-25-2014, 05:06 PM

Unfortunately it does. I'm using Excel as a database for filtering records, because the trigonometric formulas I'm applying don't play nice with Access.

This macro is just adding an extra column of information to 294,379 rows of data, with the intention of preventing an actual formula from being left in the adjacent cells. Pasting Values over top of that many formulas has crashed my Excel 3 times so far, so I went this route instead.

The end result takes the latitude and longitude of each row, and compares it to the entry of another single point, and then copies each record to a new sheet if it's less than 50 miles away, and if column column AA = something, and if AB > 0 or if AG = Exclusion, and if column Z does not contain two possible strings.

Good stuff.

It looks something like this:

Please Login or Register  to view this content.

Ultimately I'm building a data set for some blackbelts to play with, and they need everything to be clean of any formulas or anything volatile that would increase process and handling time when dealing with 300k rows.

Edit: I brought down the ranges to 50k rows at a time, stopped screen updating, and included a diagnostics tab to timestamp before and after so I can measure my runtime. 6 x 9 minutes a pop. I guess I just wanted to know if there were other options, such as Search, Find, etc.