VBA to search for multiple text strings in 2 columns

[CraigKC]

Hi,

I’ve been struggling to build a quick and speedy VBA (Excel 2013) to search for a match of 3 variables obtained from one worksheet that has 3k records, to a record in another worksheet that has over 800k records. I want to find the record that has a match for 1 variable in a cell in one column and a match for 2 variables in a cell in another column. Once I find the record, then I want to get data from other columns in that row and place on the other worksheet that I get my 3 variables. Many thanks in advance!!! Kelly

VarSub = “ARAPAHO EAST”
Sometimes lot is “LOT” or “LT”
VarLot1 = “LOT 22”
VarLot2 = “LT 22”
VarBlk = “BLK 8”
Returns match for Row 7 so I know where to get the other data for the record

Excel 2013 32 bit

	W​	X​
1​	LEGAL1	LEGAL2
2​	CAMP WISDOM WEST	BLK D LT 4
3​	ARAPAHO EAST 1 REV	BLK 3 LOT 25
4​	ARAPAHO EAST 1 REV	BLK 4 LOT 12
5​	ARAPAHO EAST 1 REV	BLK 4 LOT 16
6​	ARAPAHO EAST 2B	BLK 7 LOT 5
7​	ARAPAHO EAST 2B	BLK 8 LOT 22
8​	ARAPAHO EAST 2B	BLK 8 LOT 37
9​	ARAPAHO EAST 5	BLK B LT 1
10​	COUNTRY CLUB PARK ESTATES	BLK D LOT 8

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

 

[HackSlash]

Can you post your code?

 

[JLGWhiz]

Try this. It has three input boxes one for subdivision, one for block and one for lot, a messasge box provides the row number for a match.

Sub findParcel()
Dim subd As String, blk As Variant, lot As Long, fn As Range, lr As Long, fAdr As String
lr = Sheets("Sheet1").Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
subd = UCase(InputBox("Enter the subdivision legal name, eg. 'ELEGANT ESTATES WEST'", "SUBDIVISION"))
blk = InputBox("Enter the Block numeral or letter only, no other text.", "BLOCK DESIGNTION")
lot = CLng(InputBox("Enter the Lot numeral only, no text.", "LOT NUMBER"))
    Set fn = Sheets("Sheet1").Range("W2:W" & lr).Find(subd, , xlValues, xlWhole)
        If Not fn Is Nothing Then
            fAdr = fn.Address
            Do
                If Trim(fn.Offset(, 1).Value) Like "BLK " & blk & " L* " & lot Then
                    MsgBox "Item found on Row " & fn.Row
                    Exit Do
                End If
                Set fn = Sheets("Sheet1").Range("W2:W" & lr).FindNext(fn)
            Loop While fn.Address <> fAdr
        End If
End Sub

 

[CraigKC]

Thank JLGWhiz for posting your code. I had to add a wildcard character to the beginning of "BLK" and also at the end. I also moved the checking for the lot to a separate line since in my 800k plus rows of data, the space between the block identifier and lot could be more than just 1 space. Your code definitely was a step forward in solving my question. Your code is sort of like using the AutoFilter method. I was wondering if creating an array would be a faster method than using FIND? Here is the code that I modified above:

Sub findMatch()


Dim subd As String, blk As Variant, lot As Long, fn As Range, lr As Long, fAdr As String
lr = Sheets("Sheet1").Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
subd = UCase(InputBox("Enter the subdivision legal name, eg. 'ELEGANT ESTATES WEST'", "SUBDIVISION"))
blk = InputBox("Enter the Block numeral or letter only, no other text.", "BLOCK DESIGNTION")
lot = CLng(InputBox("Enter the Lot numeral only, no text.", "LOT NUMBER"))
    Set fn = Sheets("Sheet1").Range("W2:W" & lr).Find(subd, , xlValues, xlPart)
        If Not fn Is Nothing Then
            fAdr = fn.Address
            Do
                If Trim(fn.Offset(, 1).Value) Like "*BLK " & blk & "*" Then
                 If Trim(fn.Offset(, 1).Value) Like "*L* " & lot & "*" Then
                    MsgBox "Item found on Row " & fn.Row
                    Exit Do
                 End If
                End If
                Set fn = Sheets("Sheet1").Range("W2:W" & lr).FindNext(fn)
            Loop While fn.Address <> fAdr
        End If
End Sub

Again, I really appreciated your reply!