Problems Consolidating Range for Analysis ToolPak Regression

[nb-]

I am trying to do a number of regressions in excel (2010) using the analysis toolpak regression functions through vba;

The sheets with the data haave a number of columns with a heading (sometimes a gap) and formulas underneath that return either a value or "" (blank). The regression is against variously 1, 3 or 4 variable.

Heading 1
Formula Returning Value
Formula Returning Value
Formula Returning Value
Formula Return ""
Formula Return ""
Formula Return ""
Formula Returning Value
Formula Return ""
etc.

Obviously if I try and pass this range to the analysis toolpak, it give the error; Regression 0 Input range contains non-numeric data. because of all the "" blanks.

Is there a cunning way of using unions and intersect to parse the values to the regression function that I have failed to think of?

My immediate though was to read the data into arrays and consolidate the arrays, but this didn't work either its throwing the error; Regression - Input Y range must be a contiguous reference

Am I calling the regression function in an odd way such that it does not like my arrays as parameters or doing something else silly?

Can anyone spot what I am doing wrong or have any ideas for a way round my problems? I don't really want to have to copy the values to a new set of columns and operate off the new value columns, which is about all I can think of to do.

Workbook has a reference to ATPVBAEN.XLAM

[side point as you will see I was also having problems with getting the number of dimensions of the array after it has been transposed]

Please Login or Register  to view this content.

[Pepe Le Mokko]

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.