PDA

View Full Version : VBA - phrase search and filtering



Lef_pl
01-26-2014, 12:29 PM
Hi,

I'm newbie in VBA but I would like to write a macro which search phrase from column B (for example 12345) in cells from column A (DDK912345087D).
Could you please advise?

Right now I have something like this.

Sub Makro1()

Dim rng As Range, cell As Range
Dim arrList() As String, lngCnt As Long

Set rng = Range("B1:B10")

lngCnt = 0
For Each cell In rng
ReDim Preserve arrList(lngCnt)
arrList(lngCnt) = cell.Text
lngCnt = lngCnt + 1
Next

ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:= _
"=*arrList*", Operator:=xlAnd


End Sub

Macro is showing cells from column A containing "arrList" phrase :(.

Later I would like to copy results to another worksheet but my biggest concern is that filter issue.

lecxe
01-28-2014, 07:48 AM
Hi
Welcome to the board

Unfortunately I don't think you can use in the autofilter more than 2 keys with wildcards.

You can, however, use the Advanced Filter.

You use some cells to build the criteria and invoke the filter.

Using your example, list in column A and keys to filter in B1:B10, I use C1 and C2 to build a custom criterion and filter the list, copying the result to column E.

Please try:




Sub Makro1()

Range("C1") = "FindKey"
Range("C2").Formula = "=sumproduct(countif(a2,""*""&$b$1:$b$10&""*""))"

Columns("A").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("C1:C2"), _
CopytoRange:=Range("E1"), _
Unique:=False

End Sub