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.

Code:

---

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...

Code:

---

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

Quote:

---

Originally Posted by Dave

If your userform name is Userform1 with a Listbox1 named listbox...

Code:

---

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

Code:

---

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:

Code:

---

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

---