Snasui Excel Forum

Code: Select all

Private Sub ComboBox1_Change()

Dim i, j As Integer
With Application.WorksheetFunction
    
    i = Val(.Index(Sheets("sheet1").Range("a2:a10000"), .Match(Me.ComboBox1, Sheets("Sheet1").Range("b2:b10000"), 0)))
    ComboBox2.Value = Application.WorksheetFunction.VLookup((i), Sheets("Sheet1").Range("F2:G10000"), 2, False)
 
End With
End Sub

Code: Select all

Private Sub ComboBox1_Change()
    Dim i, j As Integer
    Dim rall As Range, r As Range
    With Sheets(1)
        Set rall = .Range("f2", .Range("f" & .Rows.Count).End(xlUp))
    End With
    With Application.WorksheetFunction
        i = Val(.Index(Sheets("sheet1").Range("a2:a10000"), .Match(Me.ComboBox1, Sheets("Sheet1").Range("b2:b10000"), 0)))
        For Each r In rall
            If r.Value = i Then
                ComboBox2.AddItem r.Offset(0, 1)
            End If
        Next r
    End With
End Sub

Code: Select all

Private Sub ComboBox1_Change()

Dim i, m, n As Integer
Dim lRow As Long
     
    
With Application.WorksheetFunction
    
    i = Val(.Index(Sheets("sheet1").Range("a2:a10000"), .Match(Me.ComboBox1, Sheets("Sheet1").Range("b2:b10000"), 0)))
    End With
    On Error Resume Next
    m = Application.WorksheetFunction.Match(i, Range("F1:F200"), 0)
    n = Application.WorksheetFunction.Match(i, Range("F1:F200"), 1)
  
With Sheets("Sheet1")
ComboBox2.RowSource = .Range(.Cells(m, 7), .Cells(n, 7)).Address
End With

End Sub

Code: Select all

'Other code
ComboBox2.Clear
With Application.WorksheetFunction
    i = Val(.Index(Sheets("sheet1").Range("a2:a10000"), .Match(Me.ComboBox1, Sheets("Sheet1").Range("b2:b10000"), 0)))
    For Each r In rall
        If r.Value = i Then
            ComboBox2.AddItem r.Offset(0, 1)
        End If
    Next r
End With