>.Find in two columns

[AlexanderBB]

Set found = .Range(rCol).Find(What:=Wanted, LookIn:=xlValues, LookAt:=xlPart)

Wanted exists in 2 parts in Column S and Column T
But I'm having no luck setting a range to both columns, (which I read is possible).
How's it done ? Also, can it be done so both xlPart and xlWhole could be applied. Thanks.

 

[AlexanderBB]

Alex, that was quite a revelation, I didn't know you could fill an array like that. I've changed my code and it's shorter and more concise. But TBH I can't see any speed increase with ? Now.
I like it better not resetting a range variable and keeping track of firstFound. Here's what I ended up with

 Dim i As Long
    Dim Arr As Variant
    Dim Match As Boolean
    Dim FirstYearRow As Long
    Dim LastYearRow As Long
    With Worksheets(SheetName)
        If TextBox1.Text > "" Then
            If lstYr > "" Then
                    YearRange lstYr, FirstYearRow, LastYearRow, .Name
                    Arr = .Range("S" & FirstYearRow & ":" & "T" & LastYearRow)
                Else
                    Arr = .Range("S2:T" & .Cells(Rows.count, "S").End(xlUp).Row)
            End If
            For i = LBound(Arr) To UBound(Arr)
                If chkWhole = True Then
                        Match = StrComp(Arr(i, 1) & " " & Arr(i, 2), TextBox1.Text, vbTextCompare) = 0
                    Else
                        Match = InStr(1, Arr(i, 1) & " " & Arr(i, 2), TextBox1.Text, vbTextCompare) > 0
                End If
                If Match = True Then
                    Set TheList = ListView6.ListItems.Add(, , "")
                    TheList.SubItems(1) = .Cells(i + 1, "C")
                    TheList.SubItems(2) = .Cells(i + 1, 9)
                    TheList.SubItems(3) = .Cells(i + 1, 10)
                    TheList.SubItems(4) = .Cells(i + 1, 12)
                    TheList.SubItems(5) = .Cells(i + 1, 13)
                    TheList.SubItems(6) = .Cells(i + 1, "S") & " " & .Cells(i + 1, "T")
                    TheList.SubItems(7) = .Cells(i + 1, "H")
                    TheList.SubItems(8) = .Cells(i + 1, "W")
                End If
            Next
        End If
    End With

I've other (similar) routines which I'll also change. Many thanks

 

[Alex Blakenburg]

You're welcome. Glad you found it helpful.

 

[AlexanderBB]

Just wondering, the way the array is set up to read columns S and T, if columns were C and H can you do the same thing?
Or must you accept all values from C to H as array items?
And, sorry another question, can you use Filter on this type of array? Seems not but I may be doing it wrong.

 

[Alex Blakenburg]

1) C to H
It is generally easier and possibly more efficient (since its only one read) to put C to H into the array at once. I have only had issues when I used a really large number of columns (with a large number of rows)
You could use 2 arrays arrC and arrH, as long as you start and finish at the same row that is easy enough to work with.
2) Filter
I have never found a good use for using the array filter function since it is only a one dimension array. It would be more useful if it kept a reference ID to the original array or data. I did see @Fuji with an interesting way of using it quite recently effectively using evaluate to store the row reference (I think it was the row reference).

 

[AlexanderBB]

I think I've made a major blue with the code in Msg 11. I'm treating the array index (i) as it were the worksheet row.
Your suggestion of a second array may be the solution. I can't see any way to get the range row into the array.
I looked for @Fuji example but didn't find it.

 

[Alex Blakenburg]

Particularly if you need to write the array back to the spreadsheet the usual practice is to start the array from the first row of actual data used. If you do that then you need to know the offset to the row number to interpret it as the row number.
In your case where you are only reading the data and want the row number just load the array in starting from row 1 so that "i" in the array = the row number.
Use the For loop starting number to ignore the rows you are not interested in.

@Fuji 's use of filter and row number was on a different thread.
I think it will only complicate matters for you while you are still trying to get a handle on using arrays.

need macro

Hello, Anybody can help me on how to make a macro to modify the lay out of my worksheet as shown in Sheet1, to be like the one shown in "To be" sheet. This is the format of report from a database that can be copied to clip board in excel. Each person has multifield downward, and each group has a...

www.mrexcel.com