Building/Creating an Array

R

Rich Cooper

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
 
K

kkknie

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

keepitcool

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 >
 
R

Rich Cooper

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

Bob Phillips

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

kkknie

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
 
R

Rich Cooper

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

kkknie

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

Alan Beban

Rich said:
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.
Click to expand...
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
 
Top