creating a range by searching another worksheet
 

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.

creating a range by searching another worksheet
 

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.

creating a range by searching another worksheet
 

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.

creating a range by searching another worksheet
 

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.

creating a range by searching another worksheet
 

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.

creating a range by searching another worksheet
 

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.

creating a range by searching another worksheet
 

Hi
NEVER attach a file to this newsgroup:
- uses too much bandwith
- most people (like me) won't open these attachments or will simply
ignore such posts
- almost never required
- could contain viruses
- etc.

Try posting some example data as PLAIN text

--
Regards
Frank Kabel
Frankfurt, Germany

"Rich Cooper" schrieb im Newsbeitrag
...
Frank here is what the data looks like

"Frank Kabel" wrote in message
...
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.

creating a range by searching another worksheet
 

Sorry about that here is what the data looks like then. I didn't know about
attaching the file. I am sorry. MNTH1

MNTH2

QTR1

QTR2

YTD1

YTD2

MKT1

MKT2

M1P16

M1P17

M1P18

M1P19

M1P20

M1P21

M2P1

M2P2

M2P3

M3P4

M3P5

M3P6

M4P1

M4P2

M4P3

M4P4


"Frank Kabel" wrote in message
...
Hi
NEVER attach a file to this newsgroup:
- uses too much bandwith
- most people (like me) won't open these attachments or will simply
ignore such posts
- almost never required
- could contain viruses
- etc.

Try posting some example data as PLAIN text

--
Regards
Frank Kabel
Frankfurt, Germany

"Rich Cooper" schrieb im Newsbeitrag
...
Frank here is what the data looks like

"Frank Kabel" wrote in message
...
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.

creating a range by searching another worksheet
 

Hi
try:
Sub test_rng()
Dim ar_values As Variant
Dim RowNdx As Long
Dim LastRow As Long
Dim FirstRow 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, 2) = "M1" Then
FirstRow = RowNnx
Exit For
End If
End With
Next
For RowNdx = FirstRow To LastRow
With Cells(RowNdx, "A")
If Left(.Value, 1) = "M" And _
--Mid(.Value, 2, 1) i 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
...
Sorry about that here is what the data looks like then. I didn't know
about
attaching the file. I am sorry. MNTH1

creating a range by searching another worksheet
 

Hey frank i am still haveing trouble with that array

this is what i got so far

Dim LastRow As Integer
Dim FirstRow As Integer
Dim rowndx As Integer

Sub findrows()
'Dim LastRow As Integer
'Dim FirstRow As Integer
Dim i As Integer
Dim x As Integer
Dim a As Integer
Dim b As Integer
Dim mearray() As String
ReDim mearray(10)
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
i = 1
x = 1
For rowndx = 1 To LastRow
If Left(Cells(rowndx, "A").Value, 1) = "M" And Mid(Cells(rowndx,
"A").Value, 2, 1) = x Then
FirstRow = rowndx
mearray(a) = Left(Cells(rowndx, "A").Value, 2)
Exit For
x = x + 1
End If
Next rowndx
"Frank Kabel" wrote in message
...
Hi
try:
Sub test_rng()
Dim ar_values As Variant
Dim RowNdx As Long
Dim LastRow As Long
Dim FirstRow 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, 2) = "M1" Then
FirstRow = RowNnx
Exit For
End If
End With
Next
For RowNdx = FirstRow To LastRow
With Cells(RowNdx, "A")
If Left(.Value, 1) = "M" And _
--Mid(.Value, 2, 1) i 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
...
Sorry about that here is what the data looks like then. I didn't know
about
attaching the file. I am sorry. MNTH1

creating a range by searching another worksheet
 

Hi
I've replied to you via private email

--
Regards
Frank Kabel
Frankfurt, Germany

"Rich Cooper" schrieb im Newsbeitrag
...
Hey frank i am still haveing trouble with that array

this is what i got so far

Dim LastRow As Integer
Dim FirstRow As Integer
Dim rowndx As Integer

Sub findrows()
'Dim LastRow As Integer
'Dim FirstRow As Integer
Dim i As Integer
Dim x As Integer
Dim a As Integer
Dim b As Integer
Dim mearray() As String
ReDim mearray(10)
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
i = 1
x = 1
For rowndx = 1 To LastRow
If Left(Cells(rowndx, "A").Value, 1) = "M" And
Mid(Cells(rowndx,
"A").Value, 2, 1) = x Then
FirstRow = rowndx
mearray(a) = Left(Cells(rowndx, "A").Value, 2)
Exit For
x = x + 1
End If
Next rowndx
"Frank Kabel" wrote in message
...
Hi
try:
Sub test_rng()
Dim ar_values As Variant
Dim RowNdx As Long
Dim LastRow As Long
Dim FirstRow 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, 2) = "M1" Then
FirstRow = RowNnx
Exit For
End If
End With
Next
For RowNdx = FirstRow To LastRow
With Cells(RowNdx, "A")
If Left(.Value, 1) = "M" And _
--Mid(.Value, 2, 1) i 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
...
Sorry about that here is what the data looks like then. I didn't
know
about
attaching the file. I am sorry. MNTH1