search and display names

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,171
Office Version
2007
Platform
Windows
Try this

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Address(0, 0) = "H1" Then
    Range("H2:J" & Rows.Count).ClearContents
    If Target.Value = "" Then Exit Sub
    Dim a() As Variant, b As Variant, i As Long, n As Long
    a = Range("A1:C" & Range("B" & Rows.Count).End(xlUp).Row).Value
    ReDim b(1 To UBound(a, 1), 1 To 3)
    n = 1
    For i = 1 To UBound(a)
      If InStr(1, LCase(a(i, 2)), LCase(Target.Value)) > 0 Then
        b(n, 1) = a(i, 1)
        b(n, 2) = a(i, 2)
        b(n, 3) = a(i, 3)
        n = n + 1
      End If
    Next
    Range("H2").Resize(UBound(b), 3).Value = b
  End If
End Sub
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,171
Office Version
2007
Platform
Windows
varios 17mar2020.xlsm
ABCDEFGHIJ
1DATENAMETEXTdam
201-marjo105/03/2020dam5
302-marjo206/03/2020dam6
403-marjo307/03/2020dam7
504-marjo408/03/2020dam8
605-mardam5
706-mardam6
807-mardam7
908-mardam8
1009-mardan9
1110-mardan10
1211-mardan11
Hoja4



With that data sample it works.
You write the name in H1 and the data is copied to H2, I2, J2 and down.

You can put your data sample here and also tell what data you are putting in cell H1.

Lastly, did you modify any of the macro?
Did you copy the complete macro?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,171
Office Version
2007
Platform
Windows
You can put the macro you are using here.
Your images are not complete, I do not see the excel rows.
You could try with the data that I am putting of sample.
 

HeRoseInThree

Board Regular
Joined
Jan 11, 2018
Messages
67
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(0, 0) = "H1" Then
Range("H2:J" & Rows.Count).ClearContents
If Target.Value = "" Then Exit Sub
Dim a() As Variant, b As Variant, i As Long, n As Long
a = Range("A1:C" & Range("B" & Rows.Count).End(xlUp).Row).Value
ReDim b(1 To UBound(a, 1), 1 To 3)
n = 1
For i = 1 To UBound(a)
If InStr(1, LCase(a(i, 2)), LCase(Target.Value)) > 0 Then
b(n, 1) = a(i, 1)
b(n, 2) = a(i, 2)
b(n, 3) = a(i, 3)
n = n + 1
End If
Next
Range("H2").Resize(UBound(b), 3).Value = b
End If
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,171
Office Version
2007
Platform
Windows
You replaced the old macro with this new one.
You put the new macro in the sheet events where you want this to happen.
In fact, first delete the old macro and make sure to put the new macro in.
 

HeRoseInThree

Board Regular
Joined
Jan 11, 2018
Messages
67
I right clicked and pasted it. Once I re-read the directions and followed them, it worked quite well.

THANKS for your help!!!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,171
Office Version
2007
Platform
Windows
Again with pleasure. Thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,095,717
Messages
5,446,118
Members
405,382
Latest member
bilsko

This Week's Hot Topics

Top