creating a range by searching another worksheet

[Rich Cooper]

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 Kabel]

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

 

[Tom Ogilvy]

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).

 

[Rich Cooper]

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]

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

 

[Tom Ogilvy]

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?

 

[Frank Kabel]

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

 

[Rich Cooper]

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]

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

 

[Rich Cooper]

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]

Hi
I've replied to you via private email