<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

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

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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 andSave.

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

Sub DualFind()Dim vFind1 As String, vFind2 As StringDim rFound As Range, lLoop As LongDim bFound As BooleanDim 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 IfEnd 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. ALLpurchases 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

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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