Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel VBA: Code to Locate Two Matches in 2 Separate Columns

Back to: Excel VBA . Got any Excel/VBA Questions? Free Excel Help

Below is some Excel VBA Macro code that can be used in any Excel Spreadsheet or Worksheet. The purpose of the code is to enhance Excel's standard Find feature found under Edit>Find.

Let's say you have 2 columns of data. You need to find the word "Black" in one column and the word "Cat" in the other. Both words must be on the same row for their to be a match. The standard Excel Find cannot do this. However, using the code below it becomes possible.

Tip: If the 2 columns are say Column "D" and Column "H", select Column "D" then hold down the Ctrl key and select Column "H".

To use the code, push Alt+F11 and go to Insert>Module. Now paste in the code below. Click the top right X to get back to Excel and Save.

Now assign the macro to a control button , textbox etc or push Alt+F8 and click the macro "DualFind" the "Run"

Sub DualFind()

Dim vFind1 As String, vFind2 As String

Dim rFound As Range, lLoop As Long

Dim bFound As Boolean

Dim rLookIn1 As Range, rLookIn2 As Range

	If Selection.Columns.Count <> 2 And Selection.Areas.Count = 1 Then

		MsgBox "Must select 2 columns to search",vbOKOnly, "ozgrid.com"

		Exit Sub

	ElseIf Selection.Areas.Count = 1 Then

			MsgBox "Must select 2 columns to search",vbOKOnly, "ozgrid.com"

			Exit Sub

	End If

	

	vFind1 = InputBox("Find What: First value?", "FIND FIRST VALUE")

		If vFind1 = vbNullString Then Exit Sub

			vFind2 = InputBox("Find What: Second value?", "FIND SECOND VALUE")

		If vFind2 = vbNullString Then Exit Sub

		If Selection.Areas.Count > 1 Then

			Set rLookIn1 = Selection.Areas(1).Columns(1)

			Set rLookIn2 = Selection.Areas(2).Columns(1)

		Else

			Set rLookIn1 = Selection.Columns(1)

			Set rLookIn2 = Selection.Columns(2)

		End If

		

		Set rFound = rLookIn1.Cells(1, 1)

			For lLoop = 1 To WorksheetFunction.CountIf(rLookIn1, vFind1)

				Set rFound = rLookIn1.Find(What:=vFind1, After:=rFound, LookAt:=xlWhole)

				 If UCase(rLookIn2.Cells(rFound.Row, 1)) = UCase(vFind2) Then

				 	bFound = True

				 	Exit For

				 End If

			Next lLoop<

		If bFound = True Then

			MsgBox "Match found", vbInformation, "ozgrid.com"

			Range(rFound, rLookIn2.Cells(rFound.Row, 1)).Select

		Else

			MsgBox "Sorry, no match found", vbInformation, "ozgrid.com"

		End If

End Sub

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel VBA Video Training/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft