-
find and compare
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!
-
What is returned if all 5 numbers are not the same? How is the result to be returned?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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.
-
[VBA]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
[/VBA]
Last edited by mdmackillop; 02-27-2011 at 03:11 PM.
Reason: No result messagebox added
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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?
-
[VBA]Fnd1 = Inputbox("Find 1")
Fnd2 = Inputbox("Find 2") [/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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
[vba] Res1.Interior.ColorIndex = 6
Res2.Interior.ColorIndex = 7 [/vba]
but I always end up with a 1004 error code!
Any ideas on that?
-
[VBA]MsgBox "Res1= " & Res1.Address & vbCr & "Res2= " & Res2.Address[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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!
-
figured it out:
[VBA]
sMessage = ""
For Each Res1 In Res1
sMessage = sMessage & CStr(Res1.Value) & vbCrLf
Next Res1
MsgBox sMessage
[/VBA]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules