I am trying to build a range by having a procedure go through a range. I
have a range of markets. The range is column A. In column A at the very
top you have some summary information then you have M1P1,
M1p2...M2p2...M3p5... I am trying to create an array that will be ("m1",
"m2", "m3"...) but i am having trouble doing this. I can't get the
procedure to go throught the range. Any help on how to do this is greatly
appreciated.

Hi
try the following (not fully tested)
sub test_rng
dim ar_values as variant
Dim RowNdx As Long
Dim LastRow As Long
dim i

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
redim ar_values(1 to lastrow)
i=0

For RowNdx = 1 To lastrow
with Cells(RowNdx, "A")
if left(.value,1)="M" then
i=i+1
ar_values(i)=left(.value,2)
end if
end with
Next RowNdx
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"Rich Cooper" schrieb im Newsbeitrag
...
I am trying to build a range by having a procedure go through a
range. I
have a range of markets. The range is column A. In column A at the
very
top you have some summary information then you have M1P1,
M1p2...M2p2...M3p5... I am trying to create an array that will be
("m1",
"m2", "m3"...) but i am having trouble doing this. I can't get the
procedure to go throught the range. Any help on how to do this is
greatly
appreciated.

Frank
Thats helping alot but i am running into some problems. Hopefully you can
help. I want the row it starts at to be M1P1 and in the array i want it tp
only count M1 once. SO the final array should look like this if i only have
a list that goes up to M4P4. ar_values("m1","m2","m3"m4") Hope that makes
it a little clear what i am trying to do.

"Frank Kabel" wrote in message
...
Hi
try the following (not fully tested)
sub test_rng
dim ar_values as variant
Dim RowNdx As Long
Dim LastRow As Long
dim i

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
redim ar_values(1 to lastrow)
i=0

For RowNdx = 1 To lastrow
with Cells(RowNdx, "A")
if left(.value,1)="M" then
i=i+1
ar_values(i)=left(.value,2)
end if
end with
Next RowNdx
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"Rich Cooper" schrieb im Newsbeitrag
...
I am trying to build a range by having a procedure go through a
range. I
have a range of markets. The range is column A. In column A at the
very
top you have some summary information then you have M1P1,
M1p2...M2p2...M3p5... I am trying to create an array that will be
("m1",
"m2", "m3"...) but i am having trouble doing this. I can't get the
procedure to go throught the range. Any help on how to do this is
greatly
appreciated.

Hi
some questions
- is your data sorted
- does it start in a fixed row

--
Regards
Frank Kabel
Frankfurt, Germany

"Rich Cooper" schrieb im Newsbeitrag
...
Frank
Thats helping alot but i am running into some problems. Hopefully
you can
help. I want the row it starts at to be M1P1 and in the array i want
it tp
only count M1 once. SO the final array should look like this if i
only have
a list that goes up to M4P4. ar_values("m1","m2","m3"m4") Hope that
makes
it a little clear what i am trying to do.

"Frank Kabel" wrote in message
...
Hi
try the following (not fully tested)
sub test_rng
dim ar_values as variant
Dim RowNdx As Long
Dim LastRow As Long
dim i

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
redim ar_values(1 to lastrow)
i=0

For RowNdx = 1 To lastrow
with Cells(RowNdx, "A")
if left(.value,1)="M" then
i=i+1
ar_values(i)=left(.value,2)
end if
end with
Next RowNdx
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"Rich Cooper" schrieb im Newsbeitrag
...
I am trying to build a range by having a procedure go through a
range. I
have a range of markets. The range is column A. In column A at
the
very
top you have some summary information then you have M1P1,
M1p2...M2p2...M3p5... I am trying to create an array that will
be
("m1",
"m2", "m3"...) but i am having trouble doing this. I can't get
the
procedure to go throught the range. Any help on how to do this
is
greatly
appreciated.

You can also do this using a collection as illustrated by John Walkenbach:

http://j-walk.com/ss/excel/tips/tip47.htm
Filling a ListBox With Unique Items

While the sample code fills a listbox, that is only a small part of the code
at the bottom. Most of the code is focused on getting a unique list (and
then sorting it

Do you mean you want it to search down the column until it finds the first
instance of M1P1 and then start there?

--
Regards,
Tom Ogilvy

"Rich Cooper" wrote in message
...
Frank
Thats helping alot but i am running into some problems. Hopefully you can
help. I want the row it starts at to be M1P1 and in the array i want it
tp
only count M1 once. SO the final array should look like this if i only
have
a list that goes up to M4P4. ar_values("m1","m2","m3"m4") Hope that
makes
it a little clear what i am trying to do.

"Frank Kabel" wrote in message
...
Hi
try the following (not fully tested)
sub test_rng
dim ar_values as variant
Dim RowNdx As Long
Dim LastRow As Long
dim i

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
redim ar_values(1 to lastrow)
i=0

For RowNdx = 1 To lastrow
with Cells(RowNdx, "A")
if left(.value,1)="M" then
i=i+1
ar_values(i)=left(.value,2)
end if
end with
Next RowNdx
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

"Rich Cooper" schrieb im Newsbeitrag
...
I am trying to build a range by having a procedure go through a
range. I
have a range of markets. The range is column A. In column A at the
very
top you have some summary information then you have M1P1,
M1p2...M2p2...M3p5... I am trying to create an array that will be
("m1",
"m2", "m3"...) but i am having trouble doing this. I can't get the
procedure to go throught the range. Any help on how to do this is
greatly
appreciated.

Sub Tester1()
Dim varr() As String
Dim bUnique As Boolean, sStr As String
Dim cell As Range, rng As Range
Dim i As Long, idex As Long
ReDim varr(0 To 0)
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
idex = 0
For Each cell In rng
sStr = Left(cell.Value, 2)
bUnique = True
For i = LBound(varr) To UBound(varr)
If UCase(sStr) = UCase(varr(i)) Then
bUnique = False
Exit For
End If
Next
If bUnique Then
varr(idex) = sStr
idex = idex + 1
ReDim Preserve varr(0 To idex)
End If
Next
ReDim Preserve varr(0 To idex - 1)
For i = LBound(varr) To UBound(varr)
Debug.Print i, varr(i)
Next

End Sub


You can also do this using a collection as illustrated by John Walkenbach:

http://j-walk.com/ss/excel/tips/tip47.htm
Filling a ListBox With Unique Items

While the sample code fills a listbox, that is only a small part of the code
at the bottom. Most of the code is focused on getting a unique list (and
then sorting it - you can leave that part of the code or keep it according
to your needs).

--
Regards,
Tom Ogilvy




"Rich Cooper" wrote in message
...
I am trying to build a range by having a procedure go through a range. I
have a range of markets. The range is column A. In column A at the very
top you have some summary information then you have M1P1,
M1p2...M2p2...M3p5... I am trying to create an array that will be ("m1",
"m2", "m3"...) but i am having trouble doing this. I can't get the
procedure to go throught the range. Any help on how to do this is greatly
appreciated.