PDA

View Full Version : find and compare



asddsa88
02-27-2011, 12:57 PM
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
02-27-2011, 01:41 PM
What is returned if all 5 numbers are not the same? How is the result to be returned?

asddsa88
02-27-2011, 02:03 PM
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
02-27-2011, 02:48 PM
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
02-27-2011, 03:09 PM
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
02-27-2011, 03:12 PM
Fnd1 = Inputbox("Find 1")
Fnd2 = Inputbox("Find 2")

asddsa88
02-28-2011, 04:53 AM
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
02-28-2011, 05:56 AM
MsgBox "Res1= " & Res1.Address & vbCr & "Res2= " & Res2.Address

asddsa88
02-28-2011, 07:37 AM
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
03-08-2011, 09:43 AM
figured it out:


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