Working with Arrays

B

baconcow

I am trying to use an array to move a group of sheets from one workbook to a
new workbook. I know how to move them ALL at once to a new workbook using:

Sheets(array("Sheet1", "Sheet2", "etc.")).Move

However, only want to sent select sheets (selected by another, working,
section of code). Here is my code, so far:

' define and setup variables
Dim create_array, all_graphs
Dim offset_var As Long
Dim array_counter As Long

offset_var = 0
array_counter = 0
' this is a list of all the sheets that CAN exist
all_graphs = Array("1.1.1 - LD AN Consumption", "1.2.1 - AN Amounts", "1.3.1
- Total AN Consumption", "2.1.1 - Tank Temperature", "2.1.2 - Fudge Point",
"2.1.3 - pH", "3.1.1 - Blender Speed", "3.1.2 - Hot Cup Density", "3.1.3 -
Viscosity", "4.1.1 - Truck Cup Densities", "4.2.1 - PE105 Calibration - AN",
"4.2.2 - PE105 Calibration - Emu", "4.2.3 - PE105 Calibration - FO", "4.3.1 -
PE106 Calibration - AN", "4.3.2 - PE106 Calibration - Emu", "4.3.3 - PE106
Calibration - FO", "4.4.1 - PE105 - Scale vs. Load", "4.4.2 - PE106 - Scale
vs. Load", "5.1.1 - Tank Temperature", "5.1.2 - Fudge Point", "5.1.3 - pH",
"6.1.1 - Blender Speed", "6.1.2 - Hot Cup Density", "6.1.3 - Viscosity",
"7.1.1 - ANFO Test", "8.1.1 - Calibration - Unit 8018", "8.1.2 - Calibration
- Unit 8025")

' there are a total of 27 potential sheets, however, only the ones with a
value of 1, found at range_create.Offset(offset_var, 0), will actually exist
While offset_var < 27
If range_create.Offset(offset_var, 0).Value = 1 Then
create_array = all_graphs(offset_var + 1)
End If
offset_var = offset_var + 1
Wend

' move chart sheets to the end of a new workbook
Sheets(create_array()).Move



The section of my code that doesn't seem to work, at all, is
Sheets(create_array()).Move. How do I implement this properly, or is there a
problem with the previous section? I originally had it say:

If range_create.Offset(0, 0) = 1 Then
Sheets(Array("1.1.1 - LD AN Consumption").Move
End If
' etc... all the way to the last one, but it only worked for the first one,
and then crashed, it didn't want to add it to the same "new" workbook or
another new one, which wasn't my needs anyways


Thanks you Jon Peltier, Peter T., Dave Peterson, and ND Pard for all your
help on that last section of code. I am still stuck working on it, but it is
not necessary to complete at this time
 
B

baconcow

I modified some stuff, but I am still getting problems. It is now telling me
that the create_array(array_counter) subscript is out of range. How come I
cannot use the array dynamically instead of how I have used the all_graphs
array? I wasn't sure whether I should use:

Dim create_array(), all_graphs()

or

Dim create_array As Variant, all_graphs As Variant
(as suggested in the MS help)


' // skip


While offset_var < 27
If range_create.Offset(offset_var, 0).Value = 1 Then
create_array(array_counter) = all_graphs(offset_var) ' this line
gives me the trouble now
array_counter = array_counter + 1
End If
offset_var = offset_var + 1
Wend

' move chart sheets to the end of a new workbook
Sheets(Array(create_array)).Move ' this may work now, it did with
slightly modified code, but with only 1 sheet in the array :S
 
T

T Lavedas

I am trying to use an array to move a group of sheets from one workbook to a
new workbook. I know how to move them ALL at once to a new workbook using:

Sheets(array("Sheet1", "Sheet2", "etc.")).Move

However, only want to sent select sheets (selected by another, working,
section of code). Here is my code, so far:

' define and setup variables
Dim create_array, all_graphs
Dim offset_var As Long
Dim array_counter As Long

offset_var = 0
array_counter = 0
' this is a list of all the sheets that CAN exist
all_graphs = Array("1.1.1 - LD AN Consumption", "1.2.1 - AN Amounts", "1.3.1
- Total AN Consumption", "2.1.1 - Tank Temperature", "2.1.2 - Fudge Point",
"2.1.3 - pH", "3.1.1 - Blender Speed", "3.1.2 - Hot Cup Density", "3.1.3 -
Viscosity", "4.1.1 - Truck Cup Densities", "4.2.1 - PE105 Calibration - AN",
"4.2.2 - PE105 Calibration - Emu", "4.2.3 - PE105 Calibration - FO", "4.3.1 -
PE106 Calibration - AN", "4.3.2 - PE106 Calibration - Emu", "4.3.3 - PE106
Calibration - FO", "4.4.1 - PE105 - Scale vs. Load", "4.4.2 - PE106 - Scale
vs. Load", "5.1.1 - Tank Temperature", "5.1.2 - Fudge Point", "5.1.3 - pH",
"6.1.1 - Blender Speed", "6.1.2 - Hot Cup Density", "6.1.3 - Viscosity",
"7.1.1 - ANFO Test", "8.1.1 - Calibration - Unit 8018", "8.1.2 - Calibration
- Unit 8025")

' there are a total of 27 potential sheets, however, only the ones with a
value of 1, found at range_create.Offset(offset_var, 0), will actually exist
While offset_var < 27
If range_create.Offset(offset_var, 0).Value = 1 Then
create_array = all_graphs(offset_var + 1)
End If
offset_var = offset_var + 1
Wend

' move chart sheets to the end of a new workbook
Sheets(create_array()).Move

The section of my code that doesn't seem to work, at all, is
Sheets(create_array()).Move. How do I implement this properly, or is there a
problem with the previous section? I originally had it say:

If range_create.Offset(0, 0) = 1 Then
Sheets(Array("1.1.1 - LD AN Consumption").Move
End If
' etc... all the way to the last one, but it only worked for the first one,
and then crashed, it didn't want to add it to the same "new" workbook or
another new one, which wasn't my needs anyways

Thanks you Jon Peltier, Peter T., Dave Peterson, and ND Pard for all your
help on that last section of code. I am still stuck working on it, but it is
not necessary to complete at this time
Click to expand...

I think in place of ...

If range_create.Offset(0, 0) = 1 Then
Sheets(Array("1.1.1 - LD AN Consumption").Move

you want something like this ...

Sheets(all_graphs(range_create.Offset(0, 0).value - 1)).Move

This presumes you want to move the sheet named as the first element in
the array named all_graphs (which is an array of names, not worksheet
objects). You must use the array's name with and index to access its
contents. I'm also guessing that the cell referenced by
range_create.Offset(0, 0) contains the index into the array fro the
sheet's name you want to move.

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/
 
B

baconcow

The cells in range.Offset(x, 0) display a 1 or 0 for whether or not they
should be created. Those 1's and 0's are turned on and off with select boxes.
I will try out your idea. Thanks
 
D

Dave Peterson

Maybe...

Option Explicit
Sub testme()

Dim create_array As Variant
Dim all_graphs As Variant
Dim offset_var As Long
Dim uCtr As Long 'UsedCounter

' this is a list of all the sheets that CAN exist
all_graphs = Array("1.1.1 - LD AN Consumption", _
"1.2.1 - AN Amounts", _
"1.3.1 - Total AN Consumption", _
"2.1.1 - Tank Temperature", "2.1.2 - Fudge Point", _
"2.1.3 - pH", "3.1.1 - Blender Speed", _
"3.1.2 - Hot Cup Density", "3.1.3 - Viscosity", _
"4.1.1 - Truck Cup Densities", _
"4.2.1 - PE105 Calibration - AN", _
"4.2.2 - PE105 Calibration - Emu", _
"4.2.3 - PE105 Calibration - FO", _
"4.3.1 - PE106 Calibration - AN", _
"4.3.2 - PE106 Calibration - Emu", _
"4.3.3 - PE106 Calibration - FO", _
"4.4.1 - PE105 - Scale vs. Load", _
"4.4.2 - PE106 - Scale vs. Load", _
"5.1.1 - Tank Temperature", "5.1.2 - Fudge Point", _
"5.1.3 - pH", "6.1.1 - Blender Speed", _
"6.1.2 - Hot Cup Density", "6.1.3 - Viscosity", _
"7.1.1 - ANFO Test", "8.1.1 - Calibration - Unit 8018", _
"8.1.2 - Calibration - Unit 8025")

'make room for all possible sheets
ReDim create_array(LBound(all_graphs) To UBound(all_graphs))

uCtr = LBound(all_graphs) - 1
For offset_var = LBound(all_graphs) To UBound(all_graphs)
If range_create.Offset(offset_var, 0).Value = 1 Then
uCtr = uCtr + 1
create_array(uCtr) = all_graphs(offset_var)
End If
Next offset_var

If uCtr < LBound(all_graphs) Then
MsgBox "Nothing exists!"
Else
ReDim Preserve create_array(LBound(all_graphs) To uCtr)
Sheets(create_array).Move
End If

End Sub

But I don't really know waht range_Create is...
 
B

baconcow

Thanks! That works. I want to dissect it and see where I was going wrong. If
you don't mind if I use a portions of this code, I will apply credit within.
Thank you.
 
D

Dave Peterson

Glad it worked.

But if you're going to apply credit, you'll have to credit the newsgroup. I'm
sure that's where I learned it!
 
B

baconcow

Definitely! As I always learned with C/C++, TI-Basic, TI 83+ Assembly,
Prolog, and Java... somebody always is going to teach you the code. It's up
to us to take all the pieces and throw it together in our own creations.

I will credit the Microsoft Excel Discussion Group. I learned almost all of
my Excel programming here, through Macro studying, and different Microsoft
help files.
 
D

Dave Peterson

I sometimes searched the google newsgroups:
http://groups.google.com/advanced_search?hl=en&q=&ie=UTF-8&hl=en&

To find the thread and then included that thread's url in a comment in the
module (or use something like snipurl.com to make the link shorter).

Every once in awhile, it would be useful.
Definitely! As I always learned with C/C++, TI-Basic, TI 83+ Assembly,
Prolog, and Java... somebody always is going to teach you the code. It's up
to us to take all the pieces and throw it together in our own creations.

I will credit the Microsoft Excel Discussion Group. I learned almost all of
my Excel programming here, through Macro studying, and different Microsoft
help files.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lots of Frustration - Lots of Arrays, Dynamic Ranges Don't Work, Help With Options 6

Top