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.

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.

On Wed, 12 Nov 2008 19:01:16 -0800 (PST), Per Jessen
wrote:

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:

Thanks Per, I'll remember that. I may have Base 1 set without
realising... element (1) seems to always be the first.

Cheers - Kirk

Your Evaluate part is hard to read for me. so, I rewrite your code. note
that array nums start from nums(0) in my code.

Function GetLines(mSheet, ta, ByVal fromLine)
Dim Rng As Range
Dim nums() As Long
Dim Lastrow As Long, co As Long
Dim iRow As Variant
Dim arr As Variant

Set Rng = Worksheets(mSheet).Range("A1") _
.SpecialCells(xlCellTypeLastCell)
Lastrow = Rng.Row
ReDim nums(Lastrow)
Do While fromLine < Lastrow
arr = Evaluate("F" & fromLine & ":F" & Lastrow & "&" _
& "G" & fromLine & ":G" & Lastrow)
iRow = Application.Match(ta, arr, 0)
If IsError(iRow) Then
Exit Do
End If
nums(co) = iRow + (fromLine - 1)
fromLine = nums(co) + 1
co = co + 1
Loop
If co 0 Then
ReDim Preserve nums(co - 1)
Else
ReDim Preserve nums(co)
End If
GetLines = nums
End Function

keiji

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.