VBA Array has extra blank column and is offset

[RS15]

OK guys, I am perplexed by this problem. With this code I basically take two matrixes (both 7 columns wide but different height) from the spreadsheet and try to stack them on top of eachother. I have dumbed down the problem a bit (considering my code is way longer). THe first loop takes all the values from the first array and places them in the top part pf the new array and the second loop sticks all the 2nd array values on the bottom of the new array.

My question comes when I try to make an array of just the last column. I used the Index worksheet function to take the last column (7) of the new matrix (Val) and assign it to the new array I am creating (PV). However, that doesn't seem to work. Instead it makes the new array the 2nd to last column (6) of Val. But when I try testing the Val matrix for the the 7th column it comes out fine. It appears to me that while the Val matrix is a CtAll by 7 matrix and the PV matrix is a CtAll by 8 matrix with the first column blank. Even though I dimmed the PV matrix as a CtAll by 7 matrix. So I can't understand why this happened and was hoping somebody could explain it to me or help me make it work the way I expect it to.

The most perplexing thing is that earlier in my code I did something very similar using the worksheet index function and it came out the way I expected it. Otherwise I would have jsut though that it had something to do with my base value (1 or 0) settings.

Please Login or Register  to view this content.

Thanks for taking the time to read this guys.

[MarvinP]

Hi,

Why use arrays? If you mean by "stack on top of each other" doing a copy and paste the top array under the bottom array, you don't need arrays.

How would you do this problem by hand? Select all the cells in the top array and copy it to the windows clipboard. Then find the first cell under the other array and do a paste the clipboard..??

Why don't you write VBA code that does this above instead of cell by cell using array variables?

It just seems you are working too hard to do something easy if you look at it by using blocks and copy and paste.

Now for a possible answer to your real question. Do array's start with a zero or 1 element? That is the normal problem when using arrays.

[RS15]

Hey MarvinP,

Thanks for the answer and the suggestions. Yes your method would work great if that is what I wanted to do. But I dummed down this code a lot to focus on the heart of my problem. In my actual code I take the ranges, do some calculations on them, then sort them and then stack them on top of each other and then do more calculations on them. So it really isn't a simple copy and paste problem like the code above looks like.

Is there any way you could explain the whole starting with 0 or 1 element thing a bit better. I know what you mean when you say this but where is this a problem in my code? Because when I assign each value to the Val array I start with 1 and that works out fine. When I reference that array and make the PV array using the index function I also start with 1 but that doesn't seem to work here. HOWEVER, earlier in my code (not shown here) I did the exact same method using the worksheet index function and it does work there. Any thoughts?

[MarvinP]

My thoughts are:

When we assume things are just like before and the answer is wrong - we assumed wrong. I do this to myself daily.

I'd suggest you set a breakpoint in your code (somewhere near the top) and step through the code and hover the mouse over variables to see what VBA thinks they are. You scratch your head and see if they make sense or what you expect.

See http://www.cpearson.com/Excel/DebuggingVBA.aspx for the explanation of what I mean.

[JosephP]

your matrices are declared with 8 columns (default base is 0 and so declaring the second dimension as just 7 is equivalent to 0 to 7)

[RS15]

Thanks again for the help. I have done that and I am sure that I am assuming something wrong but I can't seem to find the answer which is why I came here. I have tried Debugging, going through the code step by step, and I know what excel is showing for the values I just can't seem to make sense of them no matter how hard I try.

If I Dim PV(1 to CtAll, 1)
Dim Val(1 to CtAll, 7)
and then set PV = WorksheetFunction.Index(Val, 0, 7)

How are PV(1,1) and Val(1,7) different????
In actuallity PV(1,1) is equal to Val(1,6).

To get the right answer I need to set PV = WorksheetFunction.Index(Val, 0, 8)
and that doesn't make any sense to me because Val only has 7 columns AND when I go check the value of Val(1,8) there is an error.

[MickG]

The Problem I found was when you declare your column Array you used a "7" as opposed to "1 to 7". This returned an array "0 to 6".
When I changed it to "1 to 7" , all seven columns showed
I've also altered the code to express the ranges (my ranges are only 7 columns with 10 rows) as Variant arrays as you will see, that way you don't need to redim then because the size of the array is already taken care off by being a Variant array.
You can also refer to the dimensions of your susequent arrays by the the Ubound values for these initail arrays.

Please Login or Register  to view this content.

Regards Mick

[MarvinP]

I think JosephP hit the right answer. Is he right?

[RS15]

THANK YOU!! So MickG you nailed tha hammer on the head, Thank You! MarvinP was right I missed something, my previous Array where I did this I dimmed (1 to CtAll, 1 to 7)! Thanks JosephP you were also right.

OK so as a follow up question MickG: I see that you dimmed the PV and Sort PV as (1 To UBound(BvalMx, 1) + UBound(ZvalMx, 1), 1) instead of (1 To UBound(BvalMx, 1) + UBound(ZvalMx, 1), 1 to 1). Does that mean that this array has 2 columns then because of the base 0?

Also is there a reason to use the UBound and LBound over the CtZ, CtB, and CtAll values I made? I use them throughout my code and it just seems like more code to use the UBound and LBound?

Sorry I just want to make sure I fully understand this.

[MickG]

Sorry, it was a bit of an after thought dimensioning those other 2 arrays. If they are meant to be the same size as "Val" then I should have added the columns as "1 to 7".
I was'nt realy consentrating on them !!!
I don't think there's anything wrong in using other variables to define the array sizes, it just that you are doubling up on a value that is already exists and using "Ubound" gives you an automatic value of the array size as soon as you declare it as a Variant array.
Regrds Mick

[RS15]

Thank you again. You have solved pretty much ALL my problems (and I had more than just this one). I wish I could add to your reputation more than once!!

[MickG]

Your welcome
Regrds Mick