Snasui Excel Forum

Code: Select all

Public Sub searchDataMode_mode()

    Dim lr As Long, r As Range
    Dim Data_search As String, i As Integer
    Range("C17:G285").ClearContents
    id_txtbox = ""
    Data_search = Worksheets("Input").id_txtbox.Value
            With Sheets("Data")
                    lr = .Range("A" & Rows.Count).End(xlUp).Row
                    i = 17
                    For Each r In .Range("A2:A" & lr)
                        If r.Value = Data_search Then
                           Sheets("Input").Range("C" & i).Resize(1, 5).Value = _
                                        r.Offset(0, 2).Resize(1, 5).Value
                                       i = i + 1
                        End If
                    Next r
            End With
End Sub

Code: Select all

Dim lr As Long, r As Range
Dim Data_search As String, i As Integer
With Sheets("Input")
    .Range("C17:G285").ClearContents
    .Range("c49").Value = "Total"
    .Range("f49:G49").Formula = "=Sum(R17C:R[-1]C)"
    .Range("c48").Resize(30, 1).EntireRow.Insert
    id_txtbox = ""
    Data_search = .id_txtbox.Value
End With
With Sheets("Data")
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    i = 17
    For Each r In .Range("A2:A" & lr)
        If r.Value = Data_search Then
            Sheets("Input").Range("C" & i).Resize(1, 5).Value = _
                r.Offset(0, 2).Resize(1, 5).Value
            i = i + 1
        End If
    Next r
End With
With Sheets("Input")
    .Range("c" & i + 2, .Range("c" & i).End(xlDown).Offset(-1, 0)) _
        .EntireRow.Delete
End With