Visual Basic in Excel
Community Forums/General Help/Visual Basic in Excel
| ||
For a userform macro, I am wanting to search a column (named "Comments") for any non-null values, and add these to a ComboBox (CommentsPullDown). Also, I intend to put a check in to ensure that no duplicate entries are retrieved. To do this, I have made a subroutine to populate the combo box, this subroutine, however, is giving me problems. I am pretty new to VB, and so take things very carefully, as such, my code is very basic and I'm sure could be achieved a whole lot easier, but for now, it is in a format I can follow and see what's going on... most of the time :) If the debugger is accurate, my problem is in the If/End If blockks. Despite following the protocol of not having anything following 'Then' (and even including some useless Else statements too just to make sure there's something there), the compiler still seems to think I am not making Block Ifs, and breaks on End If Sub UpdateList() zIndex = 1 For zCount = 1 To 65536 zTempor = Sheets("Data").Range(Comments).Value zValid = 1 zCountAll = 0 If (zTempor = "") Then zValid = 0 Else Do zCountAll = zCountAll + 1 zTempor2 = Sheets("Data").Range(Comments).Value If (zTempor = zTempor2) Then zValid = 0 Exit Do Else zValid = zValid End If While ((zValid = 1) And (zCountAll <= zCount)) End If If (zValid = 1) Then zIndex = zIndex + 1 CommentsPullDown.ListIndex = zIndex CommentsPullDown.AddItem(zIndex) = zTempor Else zValid = zValid End If Next End Sub Can anyone help with this? If it matters, I am using Excel in OfficeXP |
| ||
At a glance there is no Loop to terminate the Do. |
| ||
Wow that was fast! Thanks a real lot, Floyd! I changed the "While" (Maybe I am too used to b3d now!) to "Loop While" I can even remove the unnecessary Else statements! I did have another error with the actual ComboBox.AddItem statment, but fixed that too: Sub UpdateList() zIndex = 1 For zCount = 1 To 65536 zTempor = Sheets("Data").Range(Comments).Value zValid = 1 zCountAll = 0 If (zTempor = "") Then zValid = 0 Else Do zCountAll = zCountAll + 1 zTempor2 = Sheets("Data").Range(Comments).Value If (zTempor = zTempor2) Then zValid = 0 Exit Do 'Else 'zValid = zValid End If Loop While ((zValid = 1) And (zCountAll <= zCount)) End If If (zValid = 1) Then zIndex = zIndex + 1 CommentsPullDown.ListIndex = zIndex CommentsPullDown.AddItem (zTempor) 'Else 'zValid = zValid End If Next End Sub Compiles fine, thanks again! |
| ||
This is interesting, do you pass the xls file to the sub updatelist ? |
| ||
Er.. I dont think so, This sub is just included int the VB code for a Userform object that I have made to maintain the xls. It's using the userform code etc. rather than the "Excel" Objects. So earlier on the calls to UpdateList are within other objects' code. I don't doubt that there's probably MUCH better ways to do what I am doing, but since I'm not very fluent with all this VB stuff, I'm taking a careful approach. |