Page 3 of 3 FirstFirst 123
Results 21 to 24 of 24

Thread: Macro/VBA to present user a look up box, and then add rows depending number of rows needed.

  1. #21
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro/VBA to present user a look up box, and then add rows depending number of rows needed.

    Hi - its the part description and I can pull that in with a look up after I get the parts exploded from kit -> part. It will change as parts get updated.

  2. #22
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,050
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Macro/VBA to present user a look up box, and then add rows depending number of rows needed.

    Ok, try this in small sample first so you can check manually whether the result is correct:
    Just change 'Sheets("sheet2")' to 'Sheets("Pairs")'

    Code:
    Sub a1110060a()
    'https://www.mrexcel.com/forum/excel-questions/1110060-macro-vba-present-user-look-up-box-then-add-rows-depending-number-rows-needed.html
    Dim i As Long, k As Long, n As Long
    Dim va, vb, vc, qx
    Dim d As Object
    Dim ws As Worksheet
    
    Sheets("sheet2").Activate  'change this to suit
    Range("A:A").NumberFormat = "@"
    
    Set ws = Sheets("Service Kits")
    
    With ws
        Set d = CreateObject("scripting.dictionary")
        va = .Range("A2:C" & .Cells(Rows.count, "A").End(xlUp).Row)
        For i = 1 To UBound(va, 1)
            d(va(i, 1)) = d(va(i, 1)) + 1
        Next
    End With
    
    vb = Range("A2:C" & Cells(Rows.count, "A").End(xlUp).Row)
    ReDim vc(1 To 100000, 1 To 3)
    k = 1
    
    For i = 1 To UBound(vb, 1)
        
        qx = vb(i, 1)
        If d.Exists(qx) Then
            
            fm = Application.Match(qx, ws.Range("A:A"), 0)
                For n = fm - 1 To fm + d(qx) - 2
                   vc(k, 1) = va(n, 3)
                   'vc(k, 2) = vb(i, 2) ' empty??
                   vc(k, 3) = va(n, 2) * vb(i, 3)
                   k = k + 1
                Next
                
        Else
                vc(k, 1) = CStr(vb(i, 1))
                vc(k, 2) = vb(i, 2)
                vc(k, 3) = vb(i, 3)
                k = k + 1
        
        End If
        
    Next
    
    Range("A2").Resize(k, 3) = vc
    
    End Sub

    Using this example:

    Excel 2013
    ABC
    1PART_IDMISC_REFERENCE
    2S81001BURNER, RPLMNT LEX485/605/730,LE,LD4854
    3S81001BURNER, RPLMNT LEX485/605/730,LE,LD4851
    4S81001BURNER, RPLMNT LEX485/605/730,LE,LD4853
    5S81004BURNER, IR SIDE ROGUE 365/425/525/6251
    6S81004BURNER, IR SIDE ROGUE 365/425/525/6251
    7N190-0001* NGZ PACK , BATTERY LED LIGHTS1
    8N475-0399-GY1SGPANEL, REAR CART GREY PRO500-11
    9S83007GRIDS, SS 525 SERIES1
    10S83007GRIDS, SS 525 SERIES1

    Sheet2





    the result:

    Excel 2013
    ABC
    1PART_IDMISC_REFERENCE
    2N100-00364
    3N305-0057-M014
    4N570-00084
    5N100-00361
    6N305-0057-M011
    7N570-00081
    8N100-00363
    9N305-0057-M013
    10N570-00083
    11N100-00531
    12N100-00531
    13N190-0001* NGZ PACK , BATTERY LED LIGHTS1
    14N475-0399-GY1SGPANEL, REAR CART GREY PRO500-11
    15N305-01011
    16N305-00992
    17N305-01011
    18N305-00992

    Sheet2




  3. #23
    New Member
    Join Date
    Apr 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro/VBA to present user a look up box, and then add rows depending number of rows needed.

    YOU ARE AMAZING! - if we ever meet - I owe you a beer!

  4. #24
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,050
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Macro/VBA to present user a look up box, and then add rows depending number of rows needed.

    OK, glad it works.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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