VBA search function

adrianbuliga

New Member
Joined
Sep 18, 2014
Messages
2
hello,
Please help me with a code for something that…i consider…complicated :confused::confused:
To be more explicit, I’ll give you an example (my table has around 500 rows and continuously piles-up):
I have a table like this:
AB
1January31
2February28
3March31
4April30
5May31
6June30

<tbody>
</tbody>

and I want a VBA macro that will search on the B column for a specific text, let’s say „ARY”, meaning that I want to search also inside the words, not only words that begin with (as an observation some cells has more than 1 word)… and return somewhere on the sheet (or on another sheet) a new table that will contain ONLY the rows that meets the criteria. In this case would be:

AB
15January31
16February28

<tbody>
</tbody>

Thank you in advance for help,
Adrian
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for results in Columns"D & E"
Code:
[COLOR="Navy"]Sub[/COLOR] MG18Sep04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] InStr(Dn.Value, "ary") > 0 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        Cells(c, "D") = Dn.Value
        Cells(c, "E") = Dn.Offset(, 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,193
Another Method..

Code:
Sub Filter_Copy_Paste()
Dim LastRow As Long
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Select
With Range("$A$1:$B$" & LastRow)
    .AutoFilter Field:=1, Criteria1:="=*ary*", Operator:=xlAnd
    .Copy Sheets("Sheet2").Range("A1")
End With
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Range("A1").Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,630
Messages
5,523,991
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top