Visual Basic in Excel

Community Forums/General Help/Visual Basic in Excel

_PJ_	(Posted 2010) [#1]

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

Floyd

(Posted 2010) [#2]

At a glance there is no Loop to terminate the Do.

_PJ_	(Posted 2010) [#3]

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!

Blitzplotter

(Posted 2010) [#4]

This is interesting, do you pass the xls file to the sub updatelist ?

_PJ_	(Posted 2010) [#5]

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.