listbox items to string with offset

[skacutter]

Hello Mr Excel,

I am trying to bounce each value in a listbox against a column in another sheet to match the value then extract email address from another column. I am using code borrowed from the web and adapting it slightly. We are getting (Runtime error 91; Object variable or with block variable not set) on the second line. we do not know how strEmail or strTo should be declared but we have tried with strEmail set as range and strTo set as string. The code is picking up the first item in the listbox and selecting the correct sheet and column to match against. what might we be doing wrong with " Set strEmail = ws.Range("A:A").Find(What:=ListBox1.List(i), LookAt:=xlWhole, MatchCase:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)"



For i = 0 To ListBox1.ListCount - 1
Set strEmail = ws.Range("A:A").Find(What:=ListBox1.List(i), LookAt:=xlWhole, MatchCase:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
strTo = strTo & ";" & strEmail.Offset(0, 2).Value
Next i


we used this post to construct >> Excel VBA - How to insert ListBox Values to a string? - Stack Overflow

 

[Roderick_E]

At a quick glance, is ws. defined further up in the code? ws. is either a sheet or a workbook right? (thus an Object)
Secondly, a form is also an object ListBox1.List may need to have the userform included, such as Userform1.Listbox1.List

 

[skacutter]

Say no more you already solved my problem

Thank You