Excel VBA show mutliple results when searching

Question

I have a sheet full of data and it will be easier if i can search the data i need.

Like an SQL Query:

SELECT * FROM table_name WHERE Name='test'

So i found this script which partially works for my case.

Sub Zoek()

Dim erow As Long
Dim ws As Worksheet
Dim Lastrow As Long
Dim count As Integer
Dim resultrow As Integer

resultrow = 11

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

count = 0
For x = 5 To Lastrow
    If Sheets("Klanten").Cells(x, 2) = Sheet1.Range("C6") Then
        Sheet1.Range("C11") = Sheets("Klanten").Cells(x, 1)
        Sheet1.Range("B11") = Sheets("Klanten").Cells(x, 2)
        Sheet1.Range("D11") = Sheets("Klanten").Cells(x, 11)
        count = count + 1
End If

Next x

End Sub

The problem with this piece of code is that, when there are multiple occurrences of the same search query it will be overwritten and only the last hit of that search will show in the cells (B11, C11, D11).

How can i make so that every match will be shown in (B11 - Bxx, C11- Cxx, D11- Dxx) ?

I know this question probably has been asked before but the solution seems so advanced.

I am a total noob in VBA so i fail to figure out how to implement those solutions.

I would appreciate if someone could provide me a solution with this code.

Excels AutoFilter will allow you to address whats in your 1st sentence .... " I have a sheet full of data and it will be easier if i can search the data .." - have you tried that ? You can also record macros and build the vba routine you need — Tin Bum, Feb 24 '19 at 18:48
Note that all your row counting variables **must** be of type `Long` because Excel has more rows than `Integer` can handle. I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. — Pᴇʜ, Feb 25 '19 at 09:24
thank you for your suggestion. Indeed a long type is 32-bit instead of the 16-bit integer. — Clumpsypenguin, Feb 25 '19 at 10:43

score 1 · Accepted Answer · answered Feb 24 '19 at 18:50

1

Since you already have that “count” variable:

For x = 5 To Lastrow
    If Sheets("Klanten").Cells(x, 2) = Sheet1.Range("C6") Then
        Sheet1.Range("C11").Offset(count) = Sheets("Klanten").Cells(x, 1)
        Sheet1.Range("B11").Offset(count) = Sheets("Klanten").Cells(x, 2)
        Sheet1.Range("D11").Offset(count)= Sheets("Klanten").Cells(x, 11)
        count = count + 1
    End If

Next x

answered Feb 24 '19 at 18:50

DisplayName

13,283
2
11
19

thank you , i found this solution to be the best for my implementation – Clumpsypenguin Feb 24 '19 at 19:17

score 0 · Answer 2 · answered Feb 24 '19 at 18:47

below code works for you.

 Sub Zoek()

Dim erow As Long
Dim ws As Worksheet
Dim Lastrow As Long
Dim count As Integer
Dim resultrow As Integer

resultrow = 11

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

count = 11
For x = 5 To Lastrow
If Sheets("Klanten").Cells(x, 2) = Sheet1.Range("C6") Then
    Sheet1.cells(count,1) = Sheets("Klanten").Cells(x, 1)
    Sheet1.cells(count,2) = Sheets("Klanten").Cells(x, 2)
    Sheet1.cells(count,11) = Sheets("Klanten").Cells(x, 11)
    count = count + 1
End If

Next x

End Sub

Excel VBA show mutliple results when searching

2 Answers2