vba help with listbox

[bensonsearch]

Hi ALL,
I will try and explain, if I make no sense let me know.

I have data in Sheet1, this is stuff like name, address, phone number so on

I have a coloum for what row it appears on as well (for referance later)

I filter the data based on different criertia in vba, great. I copy it to sheet2 to make each row a single entry and load into listbox.

eg: listbox1.additem (all from A1:G1)
listbox1.additem (all from A2:G2)

this also works fine.

however when if comes to selecting a record and then working from it im having to write a huge if statement (please excue non conventional names etc)

eg:

If ListBox1.ListIndex = 0 Then
globemail = Worksheets("Sheet2").Range("A1").value
row = Application.VLookup(globemail, Worksheets("Sheet2").Range("A1:N1"), 14, False)
If IsError(row) Then
Exit Sub
End If
Worksheets("Sheet2").Range("Y1").value = row
Worksheets("Sheet2").Range("Y2").value = "Sheet1"
Worksheets("Sheet2").Range("Y3").value = globemail
'MsgBox (row)
ElseIf ListBox1.ListIndex = 1 Then
globemail = Worksheets("Sheet2").Range("A2").value
row = Application.VLookup(globemail, Worksheets("Sheet2").Range("A2:N2"), 14, False)
If IsError(row) Then
Exit Sub
End If
Worksheets("Sheet2").Range("Y1").value = row
Worksheets("Sheet2").Range("Y2").value = "Sheet1"
Worksheets("Sheet2").Range("Y3").value = globemail
'MsgBox (row)
 
etc etc
 
End If

what i wish to know is there a better way to do this. instead of writing code for a possible infinate number of rows (i have the code there to find out how many but no idea where to go)

i just dont wanna sit here typing over 200 possible elseif statements. if we can cut this down i would be gratefull

 

[Andrew Poulsom]

Try:

With ListBox1
    If .ListIndex >= 0 Then
        row = ListIndex + 1
    End If 
End With