Results 1 to 12 of 12

VB Code to pull product ID and Qty Sold from table

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    VB Code to pull product ID and Qty Sold from table

    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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 16
    Last Post: 12-23-2017, 11:37 AM
  2. Replies: 19
    Last Post: 10-24-2017, 05:54 AM
  3. Sumif sold certain product in each month of the year
    By shmemushi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-31-2017, 01:10 PM
  4. Check if product sold more than once in 24 hours
    By mickeyog80 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-12-2015, 06:13 AM
  5. calculating product sold and per unit profit
    By 4x4 in forum Excel General
    Replies: 12
    Last Post: 01-26-2015, 01:18 PM
  6. [SOLVED] The number of times this product sold monthly
    By makinmomb in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 12-23-2014, 08:00 AM
  7. Find the last price of a product sold
    By jemamena in forum Excel General
    Replies: 6
    Last Post: 10-23-2012, 06:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1