Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'change "Sheet1" to the name of your first sheet.
wkshtSearch = "Sheet1"
'change "Sheet2" to the name of your second sheet.
wkshtInventory = "Sheet2"
Sheets(wkshtSearch).Columns("E:ZZ").EntireColumn.ClearContents
rowNum = 2
'Do Until: This will contine to run the code until the first blank A, B, and C row in the search worksheet.
Do Until Sheets(wkshtSearch).Range("A" & rowNum).Value = "" _
And Sheets(wkshtSearch).Range("B" & rowNum).Value = "" _
And Sheets(wkshtSearch).Range("C" & rowNum).Value = ""
'IF at least two search criteria have been entered, run this code.
If Sheets(wkshtSearch).Range("A" & rowNum).Value <> "" _
And Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" _
Or Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" _
And Sheets(wkshtSearch).Range("C" & rowNum).Value <> "" Then
endOfInventoryList = 2
aSearch = Sheets(wkshtSearch).Range("A" & rowNum).Value
bSearchHigh = Sheets(wkshtSearch).Range("B" & rowNum).Value + 5
bSearchLow = Sheets(wkshtSearch).Range("B" & rowNum).Value - 5
cSearch = Sheets(wkshtSearch).Range("C" & rowNum).Value
hResults = 69
h = Chr(hResults)
'Do Until: This will continue to run the code until the first blank A, B, and C row in the inventory worksheet.
Do Until Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value = "" _
And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value = "" _
And Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value = ""
secondIfBool = True
'IF Item and Amount is not blank, then run this code.
If Sheets(wkshtSearch).Range("A" & rowNum).Value <> "" _
And Sheets(wkshtSearch).Range("B" & rowNum).Value <> "" Then
If Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value Like "*" & aSearch & "*" _
And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value <= bSearchHigh _
And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value >= bSearchLow Then
Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value
hResults = hResults + 1
h = Chr(hResults)
Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value
hResults = hResults + 1
h = Chr(hResults)
Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value
hResults = hResults + 2
h = Chr(hResults)
secondIfBool = False
End If
'IF Item and Amount is not blank, but didn't find a match using the previous search method in the last IF statement, then run this code.
If secondIfBool = True Then
output = temp(Sheets(wkshtSearch).Range("A" & rowNum).Value, Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value)
If output = True _
And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value <= bSearchHigh _
And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value >= bSearchLow Then
Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value
hResults = hResults + 1
h = Chr(hResults)
Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value
hResults = hResults + 1
h = Chr(hResults)
Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value
hResults = hResults + 2
h = Chr(hResults)
End If
End If
Else
'IF Amount and Style is not blank, and it hasn't already been displayed from the previous IF statement, then run this code.
If secondIfBool = True _
And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value <= bSearchHigh _
And Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value >= bSearchLow _
And Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value = cSearch Then
Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("A" & endOfInventoryList).Value
hResults = hResults + 1
h = Chr(hResults)
Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("B" & endOfInventoryList).Value
hResults = hResults + 1
h = Chr(hResults)
Sheets(wkshtSearch).Range(h & rowNum).Value = Sheets(wkshtInventory).Range("C" & endOfInventoryList).Value
hResults = hResults + 2
h = Chr(hResults)
End If
End If
endOfInventoryList = endOfInventoryList + 1
Loop
Else
If Sheets(wkshtSearch).Range("B" & rowNum).Value = "" Then
Sheets(wkshtSearch).Range("E" & rowNum).Value = "Enter an Amount"
End If
End If
rowNum = rowNum + 1
Loop
End Sub
Function temp(a, b)
'THIS CODE DOES A PARTIAL VLOOKUP SO THAT IF THERE IS ONLY 1 DIFFERENCE BETWEEN THE TWO VALUES, THEN IT WILL COUNT AS A MATCH.
cnt1 = 1
errorCount = 0
'Do Until: Find the first character of a within b.
Do Until Mid(a, 1, 1) = Mid(b, cnt1, 1) Or cnt1 > Len(b)
cnt1 = cnt1 + 1
Loop
If cnt1 > Len(b) Then
errorCount = errorCount + 1
End If
cnt2 = 2
Do Until Mid(a, cnt2, 1) <> Mid(b, cnt2, 1) Or cnt2 > Len(a)
cnt2 = cnt2 + 1
Loop
If cnt2 < Len(a) Then
errorCount = errorCount + 1
End If
If errorCount < 2 And cnt2 < Len(a) Then
cnt2 = cnt2 + 1
Do Until Mid(a, cnt2, 1) <> Mid(b, cnt2, 1) Or cnt2 > Len(a)
cnt1 = cnt1 + 1
cnt2 = cnt2 + 1
Loop
End If
If cnt2 - 1 < Len(a) Then
errorCount = errorCount + 1
cnt2 = cnt2 + 1
Do Until Mid(a, cnt2, 1) <> Mid(b, cnt2, 1) Or cnt2 > Len(a)
cnt1 = cnt1 + 1
cnt2 = cnt2 + 1
Loop
End If
If cnt2 < Len(a) Then
errorCount = errorCount + 1
End If
If errorCount < 2 Then
temp = True
Else
temp = False
End If
End Function
