Hi

This is not the entire solution, but as the first element in an array
has number 0 unless Option Base 1 is used, you should use:

For f = 0 to UBound(TheLines)

Hopes it helps.

Regards,
Per

On 13 Nov., 03:06, kirkm wrote:
I've modified this Find routine somewhat to return
an array of line numbers where Columns G & F match string ta.

I thought it was working perfectly - until it failed, and I can't see
where or how.

For my test I entered "ColF" into $F$6, $F$83, $F$160, $F$239, $F$319
and "ColG" into $G$6, $G$83, $G$160, $G$239, $G$319
into Sheet "Formats".

The function is :-

--
Function GetLines(mSheet, ta, ByVal fromLine)
'Returns Array of line numbers in mSheet where Cols G + F = ta.
Starting from fromline
Dim Rng As Range
Dim Quit As Boolean
ReDim nums(0) As Variant
Dim lastfound, fl
Dim iRow As Long, LastRow
With Worksheets(mSheet)
* * Set Rng =
Worksheets(mSheet).Range("A1").SpecialCells(xlCell TypeLastCell)
* * LastRow = Rng.row
* * While Quit = False
* * * * On Error Resume Next
* * * * iRow = .Evaluate("Match(" & Chr$(34) & ta & Chr$(34) & ", G" &
fromLine & ":G" & LastRow & "&F" & fromLine & ":F" & LastRow & ", 0)")
* * * * On Error GoTo 0
* * * * If iRow = lastfound Then
* * * * * * Quit = True
* * * * Else
* * * * * * lastfound = iRow
* * * * * * If iRow 0 Then
* * * * * * * * ReDim Preserve nums(UBound(nums) + 1)
* * * * * * * * fl = iRow + (fromLine - 1)
* * * * * * * * nums(UBound(nums)) = fl
* * * * * * End If
* * * * End If
* * * * fromLine = fl + 1
* * *Wend
End With
Set Rng = Nothing
GetLines = nums
Erase nums
End Function
--

If I call this with :-

Sub testget()
Dim ta, f, TheLines
ta = "ColGColF": f = 6
TheLines = GetLines("Formats", ta, f)
For f = 1 To UBound(TheLines)
Debug.Print TheLines(f)
Next
End Sub

and I see
*6
*83

But increasing f to an 8 does return the correct 4 line numbers

*83
*160
*239
*319

I hope someone might see why using 6 only returns 2 of what should be
5.

Many thanks - Kirk. Apologies if this is a bit long-winded.