VBA Loop Search

[raccoon588]

The code bellow goes through my list of NAEDs and if the NAED is on the list it should activate combo boxes if the NAED is not in the list it should not activate the boxes. once this code finds a partial match it makes the combo boxes available i do not want that. It has to match the NAED in the list exactly in order for the combo boxes to be made available. what can i do to fix this?

Private Sub CmbNAED_Change()

Dim r As Long, LastRows As Long, wss As Worksheet
Set wss = Sheets("Data")
LastRows = wss.Range("D" & Rows.Count).End(xlUp).Row


For r = 2 To LastRows
If Val(Me.CmbNAED.Value) = wss.Cells(r, "D") Then
Me.CmbSealing.Enabled = True
Me.CmbSealing.Value = ""
Me.CmbVaccum.Enabled = True
Me.CmbVaccum.Value = ""
End If
Next r
end sub

 

[dmt32]

Hi,
untested but see if this update does what you want

Private Sub CmbNAED_Change()
    Dim m As Variant, arr As Variant
    Dim LastRows As Long
    Dim wss As Worksheet
    
    Set wss = Sheets("Data")
    LastRows = wss.Range("D" & wss.Rows.Count).End(xlUp).Row
    
    arr = wss.Cells(1, "D").Resize(LastRows, 1).Value2
    
    m = Application.Match(Val(Me.CmbNAED.Value), arr, 0)
    
    With Me.CmbSealing
        .Enabled = CBool(Not IsError(m))
    If Not .Enabled Then .Value = ""
    End With
    
    With Me.CmbVaccum
        .Enabled = CBool(Not IsError(m))
    If Not .Enabled Then .Value = ""
    End With
End Sub

Dave