Consulting

Results 1 to 4 of 4

Thread: help with vba loop inside listbox & search value

  1. #1
    VBAX Regular
    Joined
    Nov 2019
    Posts
    10
    Location

    help with vba loop inside listbox & search value

    hi ,

    i have a userform with a listbox & i would like to use each list item as a search value for another sheet & use this vlaue to copy data to another sheet.

    below is the code i have used for the same scenario for a combo box but i dont know how to incorporate a loop for the listbox.

    Dim FlagSH As Worksheet
    Dim PartSH As Worksheet
    Dim AddMe As Range
    Dim FindValue As Range
    Dim i As Integer
    
    
    Set FlagSH = Sheet6
    Set PartSH = Sheet4
    
    
    FindPart = Me.cboItem.Value
    
    
    Set AddMe = FlagSH.ListObjects("Flag").Range.Columns(4).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1, 0)
    
    
    If FindPart <> "" Then
    
    
    Set FindValue = PartSH.Range("A:A").Find(what:=Me.cboItem.Value, LookIn:=xlValues, LookAt:=xlWhole)
    
    
    With FlagSH
    AddMe.Offset(0, 0).Value = FindValue.Offset(0, 0).Value
    AddMe.Offset(0, 1).Value = FindValue.Offset(0, 1).Value
    '''ect, ect
    end with
    please could somone help me with this?

    thanks

  2. #2
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    If your userform name is Userform1 with a Listbox1 named listbox...
    Dim FindPart As String, FindValue As Range
    If UserForm1.ListBox1.ListIndex <> -1 Then
    FindPart = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex)
    Set FindValue = Sheet1.Range("A:A").Find(what:=FindPart, LookIn:=xlValues, LookAt:=xlWhole)
    With FlagSH
    AddMe.Offset(0, 0).Value = FindValue.Offset(0, 0).Value
    End With
    End If
    HTH. Dave

  3. #3
    VBAX Regular
    Joined
    Nov 2019
    Posts
    10
    Location
    Quote Originally Posted by Dave View Post
    If your userform name is Userform1 with a Listbox1 named listbox...
    Dim FindPart As String, FindValue As Range
    If UserForm1.ListBox1.ListIndex <> -1 Then
    FindPart = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex)
    Set FindValue = Sheet1.Range("A:A").Find(what:=FindPart, LookIn:=xlValues, LookAt:=xlWhole)
    With FlagSH
    AddMe.Offset(0, 0).Value = FindValue.Offset(0, 0).Value
    End With
    End If
    HTH. Dave
    thanks for your reply,

    i got it to work with the below

    If Me.lstItem.ListCount > 0 Then
    
    
    For i = 0 To Me.lstItem.ListCount - 1
    
    
    Set FindValue = PartSH.Range("A:A").Find(what:=Me.lstItem.Column(0, i), LookIn:=xlValues, LookAt:=xlWhole)
    Set AddMe2 = FlagSH.ListObjects("Flag").Range.Columns(4).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1, 0)
    
    
    With FlagSH
    
    
    AddMe2.Offset(0, 0).Value = FindValue.Offset(0, 0).Value
    AddMe2.Offset(0, 1).Value = FindValue.Offset(0, 1).Value
    AddMe2.Offset(0, 2).Value = FindValue.Offset(0, 2).Value
    AddMe2.Offset(0, 3).Value = FindValue.Offset(0, 3).Value
    AddMe2.Offset(0, 4).Value = FindValue.Offset(0, 4).Value
    AddMe2.Offset(0, 5).Value = FindValue.Offset(0, 5).Value
    AddMe2.Offset(0, 6).Value = FindValue.Offset(0, 6).Value
    AddMe2.Offset(0, 7).Value = FindValue.Offset(0, 7).Value
    AddMe2.Offset(0, 8).Value = FindValue.Offset(0, 8).Value
    AddMe2.Offset(0, 9).Value = FindValue.Offset(0, 9).Value
    AddMe2.Offset(0, 10).Value = FindValue.Offset(0, 10).Value
    AddMe2.Offset(0, 11).Value = FindValue.Offset(0, 11).Value
    AddMe2.Offset(0, 12).Value = FindValue.Offset(0, 12).Value
    AddMe2.Offset(0, 13).Value = FindValue.Offset(0, 13).Value
    AddMe2.Offset(0, 14).Value = FindValue.Offset(0, 14).Value
    AddMe2.Offset(0, 15).Value = FindValue.Offset(0, 15).Value
    AddMe2.Offset(0, 16).Value = FindValue.Offset(0, 16).Value
    AddMe2.Offset(0, 17).Value = FindValue.Offset(0, 17).Value
    AddMe2.Offset(0, 18).Value = FindValue.Offset(0, 18).Value
    AddMe2.Offset(0, 19).Value = FindValue.Offset(0, 19).Value
    AddMe2.Offset(0, 20).Value = FindValue.Offset(0, 20).Value
    AddMe2.Offset(0, 21).Value = FindValue.Offset(0, 21).Value
    AddMe2.Offset(0, 22).Value = FindValue.Offset(0, 22).Value
    AddMe2.Offset(0, 23).Value = FindValue.Offset(0, 23).Value
    AddMe2.Offset(0, 24).Value = FindValue.Offset(0, 24).Value
    AddMe2.Offset(0, 25).Value = FindValue.Offset(0, 25).Value
    
    
    End With
    Next i
    End If

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I'd suggest to use VBA:


    For j = 0 To lstItem.ListCount
      FlagSH.ListObjects("Flag").Range.Columns(4).Cells.Find("*").Offset(1).resize(,25)=PartSH.columns(1).Find(lstItem.Column(0, j)).resize(,15).value
    Next

Posting Permissions

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