Single Array from Multiple Ranges

[Joe Adams]

I am trying to create a single linear array with the
values from multiple ranges. The sub-routine I have
written uses an Application.InputBox to enter the various
ranges (such as $J10:$K22,$L:$L,M22:O22,P12). This
approach works fine if all the data is on one worksheet,
but if I try to enter a range from a second worksheet, I
receive a message stating "The reference you typed is not
valid. ..." Is there some way to enter data from a second
or third worksheet?

I know this can be done with the ParamArray keyword in a
function, but then how would I pass the array back to the
sub-routine.

I am using Excel 2000. Thanks for your help

Joe

 

[Tom Ogilvy]

A range has a parent which is the worksheet. There are very few things that
support working with multisheet ranges.

If you want to just have your user type in the ranges in a VBA Inputbox
(rather than Application.InputBox), then you can write code to parse the
strings to extract the ranges.

 

[Joe Adams]

Thanks for the quick answer.
Is there a way to do it with a mouse?

Joe

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might
consider something like the following:

Sub test3010()
Dim rng1 As Range, rng2 As Range, arr() As Variant
Set rng1 = Sheets(4).Range("A11")
Set rng2 = Sheets(5).Range("A11")
arr = MakeArray(rng1, rng2, 1)
End Sub

arr is a 1-based 1-dimensional array of the elements from the ranges.

Alan Beban

 

[Tom Ogilvy]

Use a separate inputbox for selections on each sheet.

 

[Joe Adams]

Tom, thanks for your input.

Joe Adams

-----Original Message-----
Use a separate inputbox for selections on each sheet.

--
Regards,
Tom Ogilvy




.

 

[Joe Adams]

Thanks for the input Alan. Your web site and your array
functions were quite helpful. You have put a lot of
thought and work into them.
Thanks again,
Joe