Building/Creating an Array
 

I am trying/want to make code a procedure to search throught my worksheet
tabs in a workbook for the mrist 3 letters being MKT and if so i want them
to take that worksheets name and start to populate an array. Also i am
trying to find a way to count the number of values in an array and store
that number to a variable. If anyone know how to do this your help is
greatly appreciated. Thanks

Building/Creating an Array
 

This will get you started.

Code
-------------------
Sub test()

Dim w As Worksheet

For Each w In ActiveWorkbook.Worksheets
If Left(w.Name, 3) = "MKT" Then

'Do something to populate an array

End If
Next

End Su
-------------------

You will have to be more specific as to what you mean by populating a
array (what will it be used for, where will the data come from).

As to how to know how many items are in an array, that is difficult t
say. If you mean that you dimensioned an array like:

Dim strArray(100) as String

and want to get how many non-blanks are in it, you would just do this:

For i = 0 to 100
If strArray(i) = "" then Exit For
Next

iArrayLenght = i -1

Post back with more detail.



--
Message posted from http://www.ExcelForum.com

Building/Creating an Array
 

If the quantity of items is not known in advance there are 2 options:
create a dynamic array and resize it when you find something OR

if you just want to build a list of objects or names ,my preferred
method would be to use a collection object.


dim cFound as collection
dim ws as worksheet

'instantiate the new collection
set cFound = new collection
'loop thru the worksheets
For each ws in activeworkbook.worksheets
if lcase(ws.name) like "mkt*" then
cFound.add ws, ws.name
end if
Next

if cFound.count 1 then
Msgbox "I found " & cfound.count & "sheets"
end if

i suggest to read VBA help on collections.

have fun!


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Rich Cooper" wrote:

I am trying/want to make code a procedure to search throught my
worksheet tabs in a workbook for the mrist 3 letters being MKT and if
so i want them to take that worksheets name and start to populate an
array. Also i am trying to find a way to count the number of values
in an array and store that number to a variable. If anyone know how
to do this your help is greatly appreciated. Thanks

Building/Creating an Array
 

I have figured out what i need with the counting the values in an array. I
use the ubound function and the lbound function substract and add 1 becasue
of a default of 0 in the array. My question to be more specific it this. I
have a workbook with 5 worksheets: Mkt1, mkt2, mkt3, mkt4, and demog. I want
to have a procedure search the worksheet tabs/names and then create an array
with the worksheets names that begin with mkt. So in the end i will have an
array of (mkt1, mkt2...)

"kkknie " wrote in message
...
This will get you started.

Code:
--------------------
Sub test()

Dim w As Worksheet

For Each w In ActiveWorkbook.Worksheets
If Left(w.Name, 3) = "MKT" Then

'Do something to populate an array

End If
Next

End Sub
--------------------

You will have to be more specific as to what you mean by populating an
array (what will it be used for, where will the data come from).

As to how to know how many items are in an array, that is difficult to
say. If you mean that you dimensioned an array like:

Dim strArray(100) as String

and want to get how many non-blanks are in it, you would just do this:

For i = 0 to 100
If strArray(i) = "" then Exit For
Next

iArrayLenght = i -1

Post back with more detail.

K


---
Message posted from http://www.ExcelForum.com/

Building/Creating an Array
 

The number of values in an array can be calculated with

myVar = UBound(aryName,1) - LBound(aryName,1) +1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rich Cooper" wrote in message
...
I am trying/want to make code a procedure to search throught my worksheet
tabs in a workbook for the mrist 3 letters being MKT and if so i want them
to take that worksheets name and start to populate an array. Also i am
trying to find a way to count the number of values in an array and store
that number to a variable. If anyone know how to do this your help is
greatly appreciated. Thanks

Building/Creating an Array
 

This should do it...

Code
-------------------
Dim strSheet() as String
Dim i As Long
Dim w AS Worksheet

Redim strSheet(1000)

i = 0
For Each w in Worksheets
If UCase(Left(w.Name,3)) = "MKT" Then
strSheet(i) = w.Name
i = i + 1
End If
Next

Redim Preserve strSheet(i-1
-------------------



--
Message posted from http://www.ExcelForum.com

Building/Creating an Array
 

Thanks for the help

One last question, i have done that now i want to insert a value at the very
end of the array. I want it to me a sheet call demog. So in the end my
array will be (mkt1, mkt2....demog) Can i use ubound to do that and if so
how? I greatly appreciate all the help.
"kkknie " wrote in message
...
This should do it...

Code:
--------------------
Dim strSheet() as String
Dim i As Long
Dim w AS Worksheet

Redim strSheet(1000)

i = 0
For Each w in Worksheets
If UCase(Left(w.Name,3)) = "MKT" Then
strSheet(i) = w.Name
i = i + 1
End If
Next

Redim Preserve strSheet(i-1)
--------------------

K


---
Message posted from http://www.ExcelForum.com/

Building/Creating an Array
 

Change to read:

Code:
--------------------
Dim strSheet() as String
Dim i As Long
Dim w AS Worksheet

Redim strSheet(1000)

i = 0
For Each w in Worksheets
If UCase(Left(w.Name,3)) = "MKT" Then
strSheet(i) = w.Name
i = i + 1
End If
Next

strSheet(i) = "demog"

Redim Preserve strSheet(i)
--------------------

K


---
Message posted from http://www.ExcelForum.com/

Building/Creating an Array
 

Rich Cooper wrote:

Thanks for the help

One last question, i have done that now i want to insert a value at the very
end of the array. I want it to me a sheet call demog. So in the end my
array will be (mkt1, mkt2....demog) Can i use ubound to do that and if so
how? I greatly appreciate all the help.
The number of elements in a one-dimensional array, myArray, is

UBound(myArray) - LBound(myArray) + 1

regardless of whether the lower bound is 0, 1 or something else.

To add another element you can use

Redim Preserve myArray(UBound(myArray) + 1)
myArray(UBound(myArray)) = "demog"

Alan Beban