Help with Finding in Ranges

[vba_Aja]

Hi, vba newbie here. I'm trying to use the set range function to search if a string exists within it. If it cant find the string it added the string to the next row. This is fine and works correctly with the following code:

Set r1 = Range(Sheets("Sheet1").Range("A1"), Sheets(Sheet1").Range("A1").End(xlDown)).Find (What:=Sheets("Sheet2").Range("A1").Value, LookAt:=xlWhole, MatchCase:=True)

where r1 returns nothing if the string in Sheets2!A1 is not found in the full range Sheets1!A:A.

What I need to do now is add another level where if it finds the string in Sheet1!A:A it also needs to check if the corresponding value fronm that row but in Column B matches the value from Sheet2!B1.

I've tried using a for loop to search through each row individually but there are up to 50,000 rows in Sheets1, and it needs to do this with thousands of strings from Sheet2! so it takes too long to complete.

Apologies if Im talking jibberish but any help would be great!

 

[Jerry Sullivan]

Hi and Welcome to the Board,

What is your desired result (what should happen when you find matches)?

Would the code need to look for more than one match of Col A & Col B, or does it only need to find up to one match per pair in Sheet2?

 

[vba_Aja]

Hi Jerry. I have 2 worksheets; 1 with existing product codes in column A and existing product group in B. And 1 with a new updated product list. Curently, Im checking if any codes from this new list are not in the existing list.

This needs expanding so it also checks if the code is in the existing sheet then does the corresponding product groups also match. If both the product code and group match then dont add the entry from the new product list. It only needs to find 1 match to return a negative response.

Any help would be great.

 

[Jerry Sullivan]

That helps me understand what you are trying to compare but I'm still a little unclear on what you want the result of the comparison to look like.
Here's one approach for you to consider that doesn't require the use of VBA.

In your two sheets with Exising and New Product Lists, you can add two columns with the formulas shown below. The first counts matches in the Product Code. A 0 in this column on the New list means a product code has been added. A 0 in this column on the Existing List means a product code has been removed.
The second column counts matches in the Product Code and Product Group. Similarly, zeroes mean that a Product Group for a given Product Code has been added/removed.
Any numbers greater than 1 means that there are duplicates.

Excel Workbook
	A	B	C	D
1	Existing Product List		Count Matches in New List
2	Codes	Groups	Code	Code & Group
3	ABC	1	1	1
4	DEF	2	1	0
5	GHI	3	1	1
6	KLM	4	1	0
7	NOP	5	1	1
8	DEF	3	1	0
Sheet

Excel Workbook
	A	B	C	D
1	New Product List		Count Matches in Existing List
2	Codes	Groups	Code	Code & Group
3	ABC	1	1	1
4	DEF	22	2	0
5	GHI	3	1	1
6	KLM	44	1	0
7	NOP	5	1	1
8	QRS	6	0	0
9	TUV	7	0	0
Sheet