Basic array code won't work?

[Granite-Granny]

I'm new to working with arrays but I don't see why this doesn't work?
Do I need to do a redim on my arrays?

Please Login or Register  to view this content.

Thanks,

GG

[LJMetzger]

Hi GG,

Here is your code slightly modified. I suggest you single step thru the debugger and use the locals window to view your data. Notice 'arr2' is empty until the 'redim' line gets executed, then 'arr2' comes to life.

There are several issues associated with the 'Redim' command:
a. Redim arr2(1 to 10, 2 to 5) changes the dimensions of a dynamic array.
b. Redim Preserve does the same thing, but preserves the current values of items in the array.
c. When using multidimensional arrays Redim Preserve can only change the last index.

Please Login or Register  to view this content.

If you don't already use 'Option Explicit', you should use 'Option Explicit' at the top of each code module. It prevents typos from ruining days and weeks of work by forcing you to declare every variable. See http://www.cpearson.com/excel/DeclaringVariables.aspx

Here are a few debugger tips to help you get started:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

Lewis

[Granite-Granny]

LJMetzger,

Thanks for the information. The code is almost working except for my

Please Login or Register  to view this content.

I'm trying to check if an array1 location is empty but I'm not sure how to code it.
I tried

Please Login or Register  to view this content.

But that didn't work. Do you have any suggestions?

The other thing that bothers me is that arr1 doesn't have a lower bound. I wanted arr1 to default to the size of the range like I did in my original code. I've thought about redefining it

Please Login or Register  to view this content.

But I need the second dimension to go all the way up to LC1 and I'm not sure how to do that or if it even possible.

[Granite-Granny]

The other thing that bothers me is that arr1 doesn't have a lower bound. I wanted arr1 to default to the size of the range like I did in my original code. I've thought about redefining it

Please Login or Register  to view this content.

But I need the second dimension to go all the way up to LC1 and I'm not sure how to do that or if it even possible.

I think I just figured out the array thing:

Please Login or Register  to view this content.

Doh!

But I still need a hand with the if statement.

GG

[LJMetzger]

To help with the if statement I need to know what the expected contents of the cell is. Is it a string or is it a number.

For example, if you are using a string (untested):

Please Login or Register  to view this content.

If it's supposed to be a number, you can use something like:

Please Login or Register  to view this content.

Lewis

[Granite-Granny]

Lewis,

I think I understand. I was looking to see if I could use a "For Each Element" but I found a MS help page that suggests not to use "for each" with arrays and recommends an approach similar to your.

To answer your question. The first column of the array is all strings. However the next columns in the array all contain numbers. I need to do an addition of those numbers later in the code. Will having string and number elements in an array cause problems?

Also I don't think there is any need to trim off leading and training blanks, there shouldn't be any. It's a computer generated blank.

GG

[Granite-Granny]

I got my code to work except that when it gets to the end of arrr1 and then adds 1 more it gives an error.
Is this error something I should worry about or just put in a "On error resume next" followed by an "on error goto 0" later on?
Or should I take care of it in my code to prevent the error?

Please Login or Register  to view this content.

btw your tip on opening up the locals window is awesome for checking contents of an array during debugging!

Thanks

[LJMetzger]

In general "On error resume next" is the tool of last resort, and is almost never used in ordinary code (i.e. code that does not interface with Excel directly).

Here is an example where it is necessary, because there is no way to control the runtime error when the sheet doesn't exist. Comment out the 'On Error Resume Next' and watch what happens.

Please Login or Register  to view this content.

In your code there is a design error. You are trying to put 10 pounds of data into a 5 pound bag (arr2). You are trying to write into the row after the last row. Since I don't know exactly what you're trying to do, I can't suggest the 'correct' fix.

However, if you change:

Please Login or Register  to view this content.

the runtime error should go away, but still may not give you the answer you want.

Lewis

[Granite-Granny]

Yeah I figured avoiding the error is not a good thing.

What I'm trying to do is sort the data in the attached workbook to give me a summary of:
1) Total Sales
2) Sales in 2014
3) The last year there was sales

I need to do it through vba code because I need to port the array to another workbook that has a bunch of other vba code that needs to interact with the array.

The problem with my vba code is that I need to look at the next value of array 1 to decide what the location value of array 2 will be and it spills over the top of the defined array 1.

My head hurts thinking about this

GG

[LJMetzger]

Hi GG,

Try the attached file.

I think you may be taking the wrong approach. I was able provide a solution that didn't need any arrays. To give you an idea about how I developed my software, I provided 3 macros:
a. First Pass - Read the source data only
b. Add preliminary Destination output to the First Pass
c. Completed solution

If you have any questions or problems, please ask.

Lewis

[Granite-Granny]

LJMetzger,

Firstly thank you very much for taking the time to do this and explain what you did. IT is really helpful for me in my understanding. I've also very impressed at your structure. Something I hadn't really thought about in the rush to get the job done.

I started off being a little confused when going through the SummaryC sub. Then I realized there was a declaration of a bunch of constants. Then everything fell into place. The only thing I didn't really understand on the first couple of passes is the following syntax:

Please Login or Register  to view this content.

I don't really understand the # and what it does.

I really like your approach to programing and it has given me some goals to aim for. I'll spend more time going through your code in the next day or two to make sure I really do have it down. I'll give you a shout if I have further questions.

GG

[LJMetzger]

Hi,

When you type in:

Please Login or Register  to view this content.

[Granite-Granny]

LJMetzger,

I've been through everything thoroughly in the last two days. I've got it dialed in now. Thank you so much for your help I've learned a ton from reading and understanding your code. I'm now armed with some pretty cool tools and ways to structure my VBA thanks to you.

Rep coming your way.

GG