How to show multiple values from excel using VBA

Question

I have an excel file that uses VBA in order to search my data sheet and display the data presented on it. The problem is that it will only return one value. I don’t understand why it will only return one value. I need it to show all the rows that have a certain ID value.

Sub Searchdata()
    Dim Lastrow As Long
    Dim count As Integer


    Lastrow = Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row

    For X = 2 To Lastrow

        If Sheets("Data").Cells(X, 1) = Sheet3.Range("B3") Then
            Sheet3.Range("A11") = Sheets("Data").Cells(X, 1)
            Sheet3.Range("B11") = Sheets("Data").Cells(X, 2)
            Sheet3.Range("C11") = Sheets("Data").Cells(X, 3) & " " & Sheets("data").Cells(X, 4) _
                                & " " & Sheets("data").Cells(X, 5) & " " & Sheets("Data").Cells(X, 6)
            Sheet3.Range("D11") = Sheets("Data").Cells(X, 7)
        End If
    Next X

End Sub




Sub PrintOut()
    Sheet3.Range("A1:D12").PrintPreview
    Sheet3.Range("A1:D12").PrintOut
End Sub

This code will produce: the following result

@Naveen solution will produce:

Answer 1

You are writing it to the same row i.e.11 that is why it is returning single row as it is being overwritten.

You can create one more variable Y for Sheet3 and then increment it in IF condition:

Sub Searchdata()
Dim Lastrow As Long
Dim count As Integer
Lastrow = Sheets("Data").Cells(Rows.count, 1).End(xlUp).Row
Y=11
For X = 2 To Lastrow
    If Sheets("Data").Cells(X, 1) = Sheet3.Range("B3") Then
       Sheet3.Cells(Y,"A") = Sheets("Data").Cells(X, 1)
       Sheet3.Cells(Y,"B") = Sheets("Data").Cells(X, 2)
       Sheet3.Cells(Y,"C") = Sheets("Data").Cells(X, 3) & " " & Sheets("data").Cells(X, 4) _
                                & " " & Sheets("data").Cells(X, 5) & " " & Sheets("Data").Cells(X, 6)
       Sheet3.Cells(Y,"D") = Sheets("Data").Cells(X, 7)
       Y = Y + 1
    End IF
Next X
End Sub