userform listbox add items but not if they are marked cancelled

[ajm]

When adding items to a listbox, how do i avoid ones marked "Do Not Use" (DNU). This is where I am picking up my listbox contents:

Private Sub FillResults()    Dim ws As Worksheet
    Dim rngFound As Range
    Dim strFirst As String
    Dim strSearch As String
    Dim strWSname As String
    Dim Excludedsheets As Variant, GetSearchArray As Variant
    Dim x As Variant
    Dim l As Long
   


    l = 1
     
    On Error GoTo ErrorHandle
    frmBookings.LbxExistBook.Clear
    strSearch = frmBookings.cboMembName.Value
    
    For Each ws In ActiveWorkbook.Sheets
    
        Excludedsheets = Array("Homepage", " ", "  ", "Trips", "Data", "First", "Last", "Members")
        x = Application.Match(ws.Name, Excludedsheets, 0)
        If IsError(x) Then
            l = l + 1


         
            Set rngFound = ws.Range("C:C").Find(strSearch, ws.Range("C4"), xlFormulas, xlWhole, xlByColumns)
            If Not rngFound Is Nothing Then
                strWSname = ws.Range("D2") & ", " & Format(ws.Range("g2"), "Ddd dd Mmm YYYY")
    
    
                With frmBookings.LbxExistBook
                    .AddItem strWSname
                End With
   
            End If
        End If
    Next ws
    Exit Sub
ErrorHandle:
    If Err.Number = 9 Then
        MsgBox ("Error")
    Else
        MsgBox (Err.Description)
    End If                                       '
End Sub

If an item is marked DNU, this is stated in column A on each sheet.

 

[Akuini]

If I understand you correctly, maybe this:

                If rngFound.Offset(, -2).Value <> "Do Not Use" Then
                With frmBookings.LbxExistBook
                    .AddItem strWSname
                End With
                End If

 

[ajm]

If I understand you correctly, maybe this:

                If rngFound.Offset(, -2).Value <> "Do Not Use" Then
                With frmBookings.LbxExistBook
                    .AddItem strWSname
                End With
                End If

I tried your suggestion above and got "Object variable or with block variable not set"

Set rngFound = ws.Range("C:C").Find(strSearch, ws.Range("C4"), xlFormulas, xlWhole, xlByColumns)
           '/// If Not rngFound Is Nothing Then
            If rngFound.Offset(, -2).Value <> "Do Not Use" Then
                strWSname = ws.Range("D2") & ", " & Format(ws.Range("g2"), "Ddd dd Mmm YYYY")
    
    
                With frmBookings.LbxExistBook
                    .AddItem strWSname
                End With
   
            End If

Is that how you meant me to use it?

 

[Akuini]

No, just add 2 lines with blue (don't change anything else):

                   With frmBookings.LbxExistBook
                    .AddItem strWSname
                End With

                [COLOR=#0000ff]If rngFound.Offset(, -2).Value <> "Do Not Use" Then[/COLOR]
                With frmBookings.LbxExistBook
                    .AddItem strWSname
                End With
               [COLOR=#0000ff] End If[/COLOR]

 

[ajm]

so have been playing around with it. it now looks like this:

Set rngFound = ws.Range("C:C").Find(strSearch, ws.Range("C4"), xlFormulas, xlWhole, xlByColumns)
            If Not rngFound Is Nothing And rngFound.Offset(, -2).Value <> "Do Not Use" Then
                strWSname = ws.Range("D2") & ", " & Format(ws.Range("g2"), "Ddd dd Mmm YYYY")
    
                With frmBookings.LbxExistBook
                    .AddItem strWSname
                End With

the error occurs when looking at the first sheet. there is nothing to find on the first tab, rngFound is empty. It should just move onto the next tab instead of throwing an error.

 

[Akuini]

Did you read post 4?
Just add 2 lines with blue (don't change anything else):

                [COLOR=#0000ff]If rngFound.Offset(, -2).Value <> "Do Not Use" Then[/COLOR]
                With frmBookings.LbxExistBook
                    .AddItem strWSname
                End With
               [COLOR=#0000ff] End If[/COLOR]

 

[ajm]

strange, I didn't see your second post (post 4) until this morning. i'll give it a go when I get into work. thanks for keeping with me. I tend to ramble my way through problems until I either get it resolved or someone on here points me in the right direction.

 

[ajm]

thanks Akuini. works as it should now.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.