Feed variable from array

Question

How can I feed variable "CatchPhrase" with value from each cell from col S...? I need to select all rows that contain value from each cell in col S.

Problem is that col S have 1996 diferent numbers, and col A have 628790 numbers..

Sub SelectManyRows()
Dim CatchPhrase As String
Dim WholeRange As String
Dim AnyCell As Object
Dim RowsToSelect As String

CatchPhrase = "10044"

'first undo any current highlighting
Selection.SpecialCells(xlCellTypeLastCell).Select
WholeRange = "A1:" & ActiveCell.Address
Range(WholeRange).Select
On Error Resume Next ' ignore errors

For Each AnyCell In Selection
 If InStr(UCase$(AnyCell.Text), UCase$(CatchPhrase)) Then
    If RowsToSelect <> "" Then
        RowsToSelect = RowsToSelect & "," ' add group separator
    End If
    RowsToSelect = RowsToSelect & Trim$(Str$(AnyCell.Row)) & ":" &  Trim$(Str$(AnyCell.Row))
 End If
Next 

On Error GoTo 0 ' clear error 'trap'
Range(RowsToSelect).Select
End Sub

Example of what I need: enter image description here

score 1 · Answer 1 · edited May 23 '17 at 12:30

1

Using the same approach as Is it possible to fill an array with row numbers which match a certain criteria without looping?

You can return an array of numbers from column A (I have used A1:A200 in this example) that match a list in S1:S9 as below

Sub GetEm()
Dim x
x = Filter(Application.Transpose(Application.Evaluate("=if(NOT(ISERROR(MATCH(A1:A200,$S$1:S9,0))),a1:a200,""x"")")), "x", False)
End Sub

The second sub does a direct selection of these cells

Sub GetEm2()
Dim x1
x1 = Join(Filter(Application.Transpose(Application.Evaluate("=if(NOT(ISERROR(MATCH(A1:A200,$S$1:S9,0))),""a""&row(a1:a200),""x"")")), "x", False), ",")
Application.Goto Range(x1)
End Sub

edited May 23 '17 at 12:30

Community

1
1

answered Oct 03 '13 at 11:25

brettdj

54,857
16
114
177

Ahh I`m not programmer.. can you please post the complete script.. I try to implement your solution... but no luck.. Thank`s in advance – Art-of-Life Oct 03 '13 at 12:42
I paste your code in VB.. and when I start it.. it popup window asking me to run macro. Than I have to choose which to run.. :-( Sheet2.GemEm or Shee2.GemEm2.. What should I do...? – Art-of-Life Oct 03 '13 at 13:16
Paste the code into a normal module (not a sheet module). Run the second code with the sheet you want it to work on active – brettdj Oct 03 '13 at 13:36
I get error Method "Range" of object "_Global" failed... ahhh... Thank`s for helping me... – Art-of-Life Oct 03 '13 at 14:05

score 0 · Answer 2 · answered Oct 03 '13 at 11:29

Consider:

Sub dural()
    Dim rS As Range, wf As WorksheetFunction
    Dim N As Long, aryS As Variant, rSelect As Range
    Dim i As Long, v As Variant
    '
    '     Make an array from column S
    '
    N = Cells(Rows.Count, "S").End(xlUp).Row
    Set wf = Application.WorksheetFunction
    Set rS = Range("S1:S" & N)
    aryS = wf.Transpose(rS)
    '
    '     Loop down column A looking for matches
    '
    Set rSelect = Nothing
    N = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To N
        v = Cells(i, 1).Value
        If v = Filter(aryS, v)(0) Then
            If rSelect Is Nothing Then
                Set rSelect = Cells(i, 1)
            Else
                Set rSelect = Union(Cells(i, 1), rSelect)
            End If
        End If
    Next i
    '
    '     Select matching parts of column A
    '
    rSelect.Select
End Sub

Feed variable from array

2 Answers2