Find is not finding the first occurrence of text in second row

Refresh

April 2019

Views

18 time

0

Here is my excel data

enter image description here

I am trying to find the cell data in row 1 and 2

arrCols = Array("APPLE", "ORANGE")
With Sheet1
    For i = LBound(arrCols) To UBound(arrCols)
        Set colHeader = .Rows(1).Find(arrCols(i), , xlValues, xlWhole, xlByColumns, xlNext, False)
        Debug.Print arrCols(i) & "=" & colHeader.Address
        Set colDiff = .Rows(2).Find("test", , xlValues, xlWhole, xlByColumns, xlNext, False)
        Debug.Print "test =" & colDiff.Address
    Next
End With

This is the current output

APPLE=$A$1
test =$D$2
ORANGE=$D$1
test =$D$2

As you can see, in row 2, Excel is not finding the first occurrence of searched data. What am I doing wrong and how do I get the first "test" address?

Expected output

APPLE=$A$1
test =$A$2
ORANGE=$D$1
test =$D$2

1 answers

0

This is because the loop will run for the whole range regardles of where it finds “test” first, so it will only output the last occurrence. You could exit loop when item is found the first time if you wsnt first occurrence to be printed.