Hello:
Please refer to attached file.
I am using below code successfully to pull the names from column A by entering part of text in cell E1.
Once i enter then it will populate the FULL NAME of the item in column G.
I need to modify this code so that it can pull corresponding ID# and Qty Sold from Coulmn B and C
Let me know if you have any questions.
Thanks.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then GoTo 10 'Exit Sub
If Intersect(Target, Range("E1")) Is Nothing Then GoTo 10 'Then Exit Sub
If Target.Cells.Count > 1 Then GoTo 10 'Then Exit Sub
Call SearchT(UCase(Trim(Target.Value)) & "*")
10
End Sub
Option Explicit
'First, mark: Tools => References: "Microsoft Scripting Runtime"
Sub SearchT(whaaat_maaan$)
Dim r&, c2h5oh$, elem
Dim dict As New Scripting.Dictionary
If Range("E1") = "" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Select-Item")
r = .Cells(.Rows.Count, "G").End(xlUp).Row
' .Range("F1:F" & r).ClearContents
r = 2
Do Until Trim(.Cells(r, 1).Value) = ""
c2h5oh = Trim(.Cells(r, 1).Value)
If UCase(c2h5oh) Like whaaat_maaan Then
If Not dict.Exists(c2h5oh) Then dict(c2h5oh) = c2h5oh
End If
r = r + 1
Loop
'Stop
For Each elem In dict.Keys
.Range("G" & .Rows.Count).End(xlUp).Offset(1, 0).Value = elem
Next
r = .Cells(.Rows.Count, "G").End(xlUp).Row
'.Range("F1").Value = "C2H5OH"
.Range("G1").Value = "List"
With .Range("G1:G" & r)
.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlYes
.EntireColumn.AutoFit
End With
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
' CopyR = Range("G2:G" & Range("G1").Value)
End Sub
Riz
Bookmarks