Find string in column, load row into array

[davedave24]

Hi, this should be simple but I'm having no luck.

Search down column A for a string, then load that row into an array (not including A, just columns B-the end).

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davedave24]

The search for the string is fine. It's searching for the postcode in a multiline textbox ("a" is the address array)

    With Sheets("Notes").Range("A:A")
        Set rngFindPC = .Find(a(UBound(a)), , xlValues, MatchCase:=False)
    End With
    
    If rngFindPC Is Nothing Then    'not found
        MsgBox "not found"
    Else
        Application.Goto rngFindPC, False       'found
        Dim i As Integer


        Dim myarray As Variant
        myarray = Range(Range(activecell.offset(0,1).address, Cells(ActiveCell.Row, 256).End(xlToLeft).Select
        'myarray = Range(Range(ActiveCell.Offset(0, 1).Address, Cells(Rows.Count, 1).End(xlToLeft)))
        'myarray = Range(ActiveCell.Offset(0, 1), Cells(Rows.Count, 1).End(xlToRight)).Value
    
        'Looping structure to look at array.
        For i = 1 To UBound(myarray)
            MsgBox myarray(i, 1)
        Next
    End If

I've tried a few different things like that. Using XlToRight seems to cause a memory overflow. I did have it only loading the first cell, not the whole row but I've changed it so many times that I'm a bit lost.

 

[PHV]

What about this ?
myarray = Range(ActiveCell.Offset(0,1), Cells(ActiveCell.Row, 256).End(xlToLeft))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davedave24]

Ah that's the one I was using, but it only returns the very first result from column B.

 

[SkipVought]

That is NOT what your code is equivalent to!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

BTW, you get a 2dim array:
For i = 1 To UBound(myarray[!], 2[/!])
[tab]MsgBox myarray([!]1, i[/!])
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886