Single Array from Multiple Ranges


J

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
 
Ad

Advertisements

T

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.
 
J

Joe Adams

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

Joe
 
A

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("A1:D1")
Set rng2 = Sheets(5).Range("A1:D1")
arr = MakeArray(rng1, rng2, 1)
End Sub

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

Alan Beban
 
J

Joe Adams

Tom, thanks for your input.

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

--
Regards,
Tom Ogilvy




.
 
Ad

Advertisements

J

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
 

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

Top