K
kirkm
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(xlCellTypeLastCell)
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.
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(xlCellTypeLastCell)
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.