if statment doesn't work..

GUY1111 · August 21st, 2010, 06:04 AM

hi,

im using windows 7, office 2007

records= the name of the sheet were all my data is on (for list populating and storing data from the user)

F= userform name

SOFTWARELIST = a listbox populated with the data (S3:S10) from "records" (using additem)

i have written the following code that has an if statment.
for some unknown reason to me, the statement does not work, even thought all the condition are there to trigger it.

the code should:
get the value of the listbox (SOFTWARELIST) ,which the user selected from the list, then go to sheet "records" and "look" for the same value in a cell - from a range (which MUST be there, because is appeared/added to that list), and delete that cell, and the next cell to the left, and finaly, delete that value from the list.

Code:

Private Sub CommandButton9_Click()

S = Sheets("records").Range("S1").End(xlDown).Row

For Each cell In Sheets("records").Range("S3:S" & S)
If cell.Value = F.SOFTWARELIST.Value Then
    CR = cell.Row
    Sheets("records").Cells(CR, 19).Delete Shift:=xlUp
    Sheets("records").Cells(CR, 20).Delete Shift:=xlUp
    
    ind = Me.SOFTWARELIST.ListIndex
    Me.SOFTWARELIST.RemoveItem (ind)
    
    End If
Next cell

End Sub

i have no idea why it won't work.

it's driving me crasy...!!

thank you.

Shasur · August 21st, 2010, 07:47 AM

Hi

At the onset your code looks good. There might be a chance that

1) No items in the ListBox is selected

or

2)

Cell S2 is blank, which will make the following statement to return 2

S = Sheets("records").Range("S1").End(xlDown).Row

Cheers
Shasur

GUY1111 · August 22nd, 2010, 01:58 PM

i have come up with a way to get around it. (this time...)

i deleted the if statement altogether and insted used references using the listindex with cell address to "find" the cells to delete/edit.

which, by the way, works perfectly.
But i'm still in the dark as far as that if statment goes...

did anyone else have this problem before?
i still wonder what went wrong.