Find Values In List

zotee98 · Jul 26th 2006

I Want To Add A Function To A Worksheet That Allows A User To Type A Word And It To Return Matches And Their Corresponding Number In A Different Column. For Example If A User Types In "new" In Cell A1 I Would Like For The Sheet To Return "new Deal" "1"; "new Order" "2"; "new Meal" "3"; And So One Until All Values Of "new" Are Returned. I Would Like These To Be Reutrned In A Pop Up Window If Possible.

I Know This Might Be Tricky But This Forum Is Great -

Thanks In Advance!

Wigi · Jul 26th 2006

Re: Find Values In List

Hi Zotee

with a selection change event, this becomes:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    Dim rngToEval As Range, r As Range, msg As String, counter As Long
    Set rngToEval = Range("B:B").SpecialCells(xlCellTypeConstants)
    msg = "Resulting matches:" & vbCrLf
    counter = 0
    For Each r In rngToEval
        If InStr(1, r.Value, Range("A1")) > 0 Then
            counter = counter + 1
            msg = msg & vbCrLf & counter & vbTab & r.Value & vbTab & "row " & r.Row
        End If
    Next
    MsgBox msg
End If
End Sub

Display More

Cheers

Wigi

EDIT

PS: can you please change the capital at each new word... Doesn't read easily I find.

zotee98 · Jul 26th 2006

Re: Find Values In List

Sorry about the caps thing. I type everything in all caps and when it is posted to changes to first letter caps -

Your post works excellent. The only thing is that can it be adapted to return information in column B:B and column C:C so if B2 = "new gear" and E2 = "1234472" it will return this instead of the row it is located on.

The code works great though. Thanks for your help.

Aaron Blood · Jul 26th 2006

Re: Find Values In List

Quote from zotee98

Sorry about the caps thing. I type everything in all caps and when it is posted to changes to first letter caps

Oh man... that's just askin for trouble... On any message board.

You're better off all LC if you must.

Wigi · Jul 26th 2006

Re: Find Values In List

Like this?

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    Dim rngToEval As Range, r As Range, msg As String, counter As Long
    Set rngToEval = Columns("B:C").SpecialCells(xlCellTypeConstants)
    msg = "Resulting matches:" & vbCrLf
    counter = 0
    For Each r In rngToEval
        If InStr(1, r.Value, Range("A1")) > 0 Then
            counter = counter + 1
            msg = msg & vbCrLf & counter & vbTab & r.Value & vbTab & Range("E" & r.Row)
        End If
    Next
    MsgBox msg
End If
End Sub

Display More

Dave Hawley · Jul 27th 2006

Re: Find Values In List

Yes, please don't use Caps for all letters. It is considered SHOUTING!

zotee98 · Jul 27th 2006

Re: Find Values In List

i cant get this to run correctly. i am try to reference cell G1 on the "INPUT SHT" and this code is pasted on the "items" sheet. nothing happens when i put something in cell "G1" on the sht 1.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Sheets("SHT 1").Range("G1") Then
        Dim rngToEval As Range, r As Range, msg As String, counter As Long
        Set rngToEval = Columns("A:E").SpecialCells(xlCellTypeConstants)
        msg = "Resulting matches:" & vbCrLf
        counter = 0
        For Each r In rngToEval
            If InStr(1, r.Value, Range("L1")) > 0 Then
                counter = counter + 1
                msg = msg & vbCrLf & counter & vbTab & r.Value & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & Range("A" & r.Row)
            End If
        Next
        MsgBox msg
    End If
End Sub

Display More

zotee98 · Jul 27th 2006

Re: Find Values In List

also - is there a better way to line up the data in the returned message box. i have a great deal of tab's and they still dont line up correctly. please help.

Wigi · Jul 27th 2006

Re: Find Values In List

Does this work?

You need to put the code in the sheet "Input sheet" (or whatever). Not in an ordinary module or elsewhere.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("G1") Then
        Dim rngToEval As Range, r As Range, msg As String, counter As Long
        Set rngToEval = Columns("A:E").SpecialCells(xlCellTypeConstants)
        msg = "Resulting matches:" & vbCrLf
        counter = 0
        For Each r In rngToEval
            If InStr(1, r.Value, Range("L1")) > 0 Then
                counter = counter + 1
                msg = msg & vbCrLf & counter & vbTab & r.Value & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & Range("A" & r.Row)
            End If
        Next
        MsgBox msg
    End If
End Sub

Display More

About the alignment: you shouldn't do that in a message box, of course. What about placing the results in some columns you choose on beforehand?

zotee98 · Jul 27th 2006

Re: Find Values In List

that would be fine exporting them to a sheet. but i dont know where to even start that process.

zotee98 · Jul 27th 2006

Re: Find Values In List

wow i keep coming up with these little questions - how does excel determine how many results to return. it seems to be very random and the msg box size is not always the same. it seems to max out at 23 returns.

Wigi · Jul 27th 2006

Re: Find Values In List

Quote from zotee98

it seems to be very random and the msg box size is not always the same. it seems to max out at 23 returns.

Yet another reason to abandon the use of the message box.

Code

Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Address = Range("G1") Then 
        Dim rngToEval As Range, r As Range, msg As String, counter As Long 
        Set rngToEval = Columns("A:E").SpecialCells(xlCellTypeConstants) 
        msg = "Resulting matches:" & vbCrLf 
        counter = 0 
        For Each r In rngToEval 
            If InStr(1, r.Value, Range("L1")) > 0 Then 
                counter = counter + 1 
With Range("P" & counter)
.Offset(0,0).Value = counter
.Offset(0,1).Value = r.Value
.Offset(0,2).Value = Range("A" & r.Row)
End With
            End If 
        Next 
        MsgBox msg 
    End If 
End Sub

Display More

Is this what you want?

Wigi

Find Values In List

Participate now!

Share

Users Viewing This Thread