jeremybritz
New Member
- Joined
- Jul 25, 2019
- Messages
- 9
The question is in the title, but I've attached a screenshot\edit to sort of get at what I'm trying to do. Let me know if you need any clarification on what I'm trying to accomplish!
![nwFkT71.jpg](/board/proxy.php?image=https%3A%2F%2Fi.imgur.com%2FnwFkT71.jpg&hash=4113f8111769be26e23b9125b5aa9f76)
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
Dim fStr As String: fStr = TextBox1.Value
Dim AR() As Variant: AR = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim SP() As String
For i = 1 To UBound(AR)
SP = Split(AR(i, 1), ",")
For j = LBound(SP) To UBound(SP)
If SP(j) = fStr Then AL.Add i
Next j
Next i
Range("C1").Resize(AL.Count, 1).Value = Application.Transpose(AL.toarray)
End If
End Sub
This code assumes that your comma separated data is in column A beginning in A1. I added an activeX textbox object to the sheet. The code for the textbox:
Code:Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Then Dim fStr As String: fStr = TextBox1.Value Dim AR() As Variant: AR = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList") Dim SP() As String For i = 1 To UBound(AR) SP = Split(AR(i, 1), ",") For j = LBound(SP) To UBound(SP) If SP(j) = fStr Then AL.Add i Next j Next i Range("C1").Resize(AL.Count, 1).Value = Application.Transpose(AL.toarray) End If End Sub
The code fires when you hit 'Enter'. The rows that have the value from the textbox will be output to column C.
I am sure that a fair bit of this code will need to be adjusted since you didn't give a clear example of where your data is or what you want to do with the found results.
Excel Workbook | ||||
---|---|---|---|---|
A | B | |||
1 | apple | |||
2 | FruitsStill-Life Items | |||
3 | ||||
4 | ||||
5 | Name of Category | Keywords | ||
6 | Fruits | apple, banana, orange,dragonfruit | ||
7 | Cars | beetle, malibu, camaro | ||
8 | Inedible Objects | table, chair, glasses | ||
9 | Still-Life Items | apple, table | ||
10 | ||||
List Items |
Welcome to the MrExcel board!
Here is one way.
1. Make your data (A5:B9 in your example) into a formal table (if not already). That is, select the data then on the menus Insert -> Table -> My table has headers -> OK
This will mean the method should continue to automatically adjust if new data is added to that table.
2. Select B2 and format as 'Wrap Text'
3. Enter the formula shown in B2. Note that the table name needs to match your actual table name and the items in [] need to match your actual table headings.
This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.
4. Enter your value in B1.
List Items
A B 1 apple 2 Fruits
Still-Life Items3 4 5 Name of Category Keywords 6 Fruits apple, banana, orange,dragonfruit 7 Cars beetle, malibu, camaro 8 Inedible Objects table, chair, glasses 9 Still-Life Items apple, table 10
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:217px;"><col style="width:251px;"></colgroup><tbody>
</tbody>
Spreadsheet Formulas
Cell Formula B2 {=TEXTJOIN(CHAR(10),TRUE,IF(ISNUMBER(SEARCH(", "&B1&", ",", "&Table1[Keywords]&", ")),Table1[Name of Category],""))}
<tbody>
</tbody>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
BTW, for the future, you will get more help & faster if you provide your sample data in a form that can be copied into our own spreadsheet for testing. My signature block below has more help on that.
Sounds like you may have skipped step 2? As you can see in my screen shot, the results are shown on separate lines.Is there any way to format the resulting data? Currently it all displays on one line, I would like to possible display it on a separate line for each result.
Sounds like you may have skipped step 2? As you can see in my screen shot, the results are shown on separate lines.
BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B1"), Target) Is Nothing Then
Dim fStr As String: fStr = Target.Value
Dim AR() As Variant: AR = Range("A6:B" & Range("A" & Rows.Count).End(xlUp).Row).Value
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim SP() As String
For i = 1 To UBound(AR)
SP = Split(AR(i, 2), ", ")
For j = LBound(SP) To UBound(SP)
If SP(j) = fStr Then AL.Add AR(i, 1)
Next j
Next i
Range("B2").Value = Join(AL.toarray, vbLf)
End If
End Sub