---

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

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

 

See also:

Matching ComboBox Controls

Adding a Description and Category to your User Defined Functions in Excel

Excel VBA: Disable Cut/Copy in Excel Workbooks

Excel VBA: Does Workbook Exist

Do While Loop, Repeat a Block of VBA Macro Code

 

Free Training Course: Lesson 1 - Excel Fundamentals

 

See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

sexedate geneve

Gallery

---

• 
• 
• 
• 
• 
•