Snasui Excel Forum

Code: Select all

Private Sub ComboBox10_Change()

Dim R, xCell, ws
Sheets("information").Range("A3").Value = ComboBox10.Value

R = 1
For ws = 2 To ThisWorkbook.Sheets.Count
    For Each xCell In Worksheets(ws).UsedRange
        If xCell = ThisWorkbook.Sheets(3).Cells(1, 3).Value Then
        R = R + 1
        
        Worksheets(3).Cells(R + 2, 1).Value = xCell.Offset(0, 0).Value
        Worksheets(3).Cells(R + 2, 1).Value = xCell.Offset(0, 1).Value
        Worksheets(3).Cells(R + 2, 1).Value = xCell.Offset(0, 2).Value
        End If
    Next xCell
    Next ws

End Sub

Code: Select all

Dim rsAll As Range, rs As Range, rt As Range
With Sheets("information")
    Set rsAll = .Range("a6", .Range("a" & .Rows.Count).End(xlUp))
    For Each rs In rsAll
        If rs.Value = CInt(Me.ComboBox10.Value) Then
            With .Parent.Worksheets("cal")
                Set rt = .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0)
                rt.Resize(1, 24).Value = rs.Resize(1, 24).Value
            End With
        End If
    Next rs
End With