[Karl Thompson]

Given a fixed range, say A1:AD120.

The range is sorted by the contents of column "L" (which happens to get its
value by linking to a named range in other workbooks if that's important).
Column "L" can only hold one of three possible values, say "A", "B", and
"C".


The problem at hand is to write code that will return the row number that
contains the first instance of each value. There is not necessarily a value
"A".

Being a VBA novice, can someone please show me the most efficient way I
might do this? Do I have to iterate over each row?

TIA

[Tom Ogilvy]

Dim res as Variant
res = Application.Match("A",Range("L1:L120"),0)
if not iserror(res) then
rwA = res
end if
res = Applicaton.match("B",Range("L1:L120"),0)
if not iserror(res) then
rwB = res
end if
res = Application.match("C",Range("L1:L120"),0)
if not iserror(res) then
rwC = res
End if

Regards,
Tom Ogilvy

"Karl Thompson" wrote in message
...

Given a fixed range, say A1:AD120.

The range is sorted by the contents of column "L" (which happens to get
its
value by linking to a named range in other workbooks if that's important).
Column "L" can only hold one of three possible values, say "A", "B", and
"C".


The problem at hand is to write code that will return the row number that
contains the first instance of each value. There is not necessarily a
value
"A".

Being a VBA novice, can someone please show me the most efficient way I
might do this? Do I have to iterate over each row?

TIA

[Karl Thompson]

Thank you.


"Tom Ogilvy" wrote in message
...
Dim res as Variant
res = Application.Match("A",Range("L1:L120"),0)
if not iserror(res) then
rwA = res
end if
res = Applicaton.match("B",Range("L1:L120"),0)
if not iserror(res) then
rwB = res
end if
res = Application.match("C",Range("L1:L120"),0)
if not iserror(res) then
rwC = res
End if

Regards,
Tom Ogilvy

"Karl Thompson" wrote in message
...

Given a fixed range, say A1:AD120.

The range is sorted by the contents of column "L" (which happens to get
its
value by linking to a named range in other workbooks if that's
important).
Column "L" can only hold one of three possible values, say "A", "B", and
"C".


The problem at hand is to write code that will return the row number
that
contains the first instance of each value. There is not necessarily a
value
"A".

Being a VBA novice, can someone please show me the most efficient way I
might do this? Do I have to iterate over each row?

TIA