find and compare

[asddsa88]

Hello,

this shouldn't be too hard but I am stuck!!

I have a dataset with multiple entries.

Each entry is formed by 2 rows, the first row defines the entry name and the second row defines the values (1 value in each column) associated with that name.

example:
entryname = abc
entryvalue = 1,2,3,4,5


I need to create a search prompt that:

has 2 boxes, box 1 and box 2.

When I enter the search queries in box 1&2, the function searches for entrynames and finds the 2 unique entrynames that I am looking for.

When the macro finds the entryname that I need, it shifts 1 row down and selects all the entryvalues and defines them as range.

Then I need to compare the 2 ranges and find only the values that are present in both ranges.
(I was thinking to use hlookup or maybe conditional formatting for this, but I am not completely sure!)



example:

search query:
searchbox1="def"
searchbox2="ghi"

dataset:

entryname = abc
entryvalue = 1,2,3,4,5
entryname = def
entryvalue = 1,2,3,4,5
entryname = ghi
entryvalue = 1,2,3,4,5

the macro finds the 2 entryvalues associated with def and ghi and returns 2 lists in full because in this case they are exactly the same (1,2,3,4,5)

thanks in advance for any suggestions!

[mdmackillop]

What is returned if all 5 numbers are not the same? How is the result to be returned?

[asddsa88]

Thanks for the reply!

Just a clarification: the example rows that I wrote both contain 5 characters but they are actually made of a variable number of chars!

To answer your question:
An empty result dataset would be enough, or just a popup textbox saying: no matches.

[mdmackillop]

Sub Test()
Dim Fnd1, Fnd2
Dim Rng1 As Range
Dim Rng2 As Range
Dim Res1 As Range
Dim Res2 As Range

Fnd1 = [G3]
Fnd2 = [G4]
Set Rng1 = Columns(1).Find(Fnd1).Offset(1).EntireRow.SpecialCells(xlCellTypeConstants)
Set Rng2 = Columns(1).Find(Fnd2).Offset(1).EntireRow.SpecialCells(xlCellTypeConstants)
For Each cel In Rng1
Set c = Rng2.Find(cel)
If Not c Is Nothing Then
If Res1 Is Nothing Then
Set Res1 = cel
Set Res2 = c
Else
Set Res1 = Union(Res1, cel)
Set Res2 = Union(Res2, c)
End If
End If
Next
If Res1 Is Nothing Then
MsgBox "No Result"
Else
Res1.Interior.ColorIndex = 6
Res2.Interior.ColorIndex = 7
End If
End Sub

[asddsa88]

Thank you for the code, but I am not fully sure on how to use it!

How can I create a popup box that asks me the 2 search parameters everytime I run the macro?

[mdmackillop]

Fnd1 = Inputbox("Find 1")
Fnd2 = Inputbox("Find 2")

[asddsa88]

Thanks for the code, now everything is starting to work!!

I have been spending the last 20 minutes to figure out how to display res1 range in a msgbox, instead of the

Res1.Interior.ColorIndex = 6
Res2.Interior.ColorIndex = 7
but I always end up with a 1004 error code!

Any ideas on that?

[mdmackillop]

MsgBox "Res1= " & Res1.Address & vbCr & "Res2= " & Res2.Address

[asddsa88]

this shows the cells that contain the results, but I would like to show the cell values directly one after the other (separated by a space or "-" sign)!

Is it possible?

thanks in advance!

[asddsa88]

figured it out:


sMessage = ""
For Each Res1 In Res1
sMessage = sMessage & CStr(Res1.Value) & vbCrLf
Next Res1
MsgBox sMessage