Rasscal
01-23-2020, 03:19 AM
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
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
Rasscal
01-24-2020, 12:45 AM
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.