faster way to loop

[mattress58]

I have a pivot table and I need the fastest way to pull values below.


In Workbooks("ABC").Sheets("AAA").Range("A24")
need to get column D where:
column A = "Dog"
column B = 42248
column C = "San Jose"



there are about 85,000 rows that varies some each day


Here is my current code

Please Login or Register  to view this content.

[mikeTRON]

You could try filtering on the entire dataset then doing all the work at once, then unfiltering.

[mehmetcik]

This is a very slow method.

You are actually looking at each cell in column A. Ok if there only a few rows

But when there are thousands this is very slow.

Try using the Find function.

There are two approaches that I would use Depending on your data.

Solution A:

Search for your search data in column A and check if B and C also match.

Solution B:
use Countif on columns A, B and C to find how many times your search data appears in those columns.
If we search for the least common result then we could be minimising our work.

Please Login or Register  to view this content.

[mattress58]

I am trying the Find Function and it works with 2 variables, but how do I get 3 variables? Can you see what is wrong? Thanks for the help

Here is what works with 2:

Please Login or Register  to view this content.

Here is what I tries with 3

Please Login or Register  to view this content.

[mattress58]

I got it thanks. I didnt have the cVal As String. Thanks for the help