Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 21st, 2010, 06:04 AM
Registered User
Join Date: Aug 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default if statment doesn't work..


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.

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.
Old August 21st, 2010, 07:47 AM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts


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

1) No items in the ListBox is selected



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

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

C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
Old August 22nd, 2010, 01:58 PM
Registered User
Join Date: Aug 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default ok so far i have not yet found the problem, but...

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Except statment rekap@comcast.net SQL Server 2005 0 August 15th, 2009 02:41 PM
Help with IIF Statment Corey Access 2 December 14th, 2006 11:10 AM
if statment knightm28 Classic ASP Databases 2 April 1st, 2005 11:08 AM
submit in If statment not work abbylee26 Javascript How-To 1 February 11th, 2004 12:22 AM
SQL statment arshad mahmood VB Databases Basics 1 July 1st, 2003 02:10 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.