Excel VBA: Passing a variable sized two dimensional array to user selected range

Question

Thanks in advance for your time!

So, my code is designed to allow a user to select a range that contains data, which is passed into an array. I use a do while loop to manipulate the data and pass it into a new array, the dimensions of which are a function of the size of the first one.

What I'd like to do is allow the user to select a cell to serve as the top left of the destination range for this new array, much like one can identify the starting point for an ANOVA table and such.

I've been trying to use an Application.Inbox for the user to select the cell, but it's just not working.

Here's generally what I've got:

'Allocate Array
arrayCustNum = Application.InputBox("Select Customer Number Range", "Customer Range", Type:=8)
arrayData = Application.InputBox("Select Data Range (no headers or cust numbers)", "Data Range", Type:=8)
arrayDataHeader = Application.InputBox("Select Data Header Range", "Data Header Range", Type:=8)    

'Allocate Destination Array
ReDim arrayDest(UBound(arrayData, 1) * UBound(arrayData, 2) + 2, UBound(arrayData, 2))

The loop happens, and then we move to output:

Set rngPos = Range(Application.InputBox("Event Table will be size (" & UBound(arrayDest, 1) & _
", " & UBound(arrayDest, 2) & "). Choose the top left cell of desired location.", _
"Output Location", Type:=64))

rngPos.Resize(UBound(arrayDest, 1), UBound(arrayDest, 2)).Value = arrayDest

An error is thrown that simply says "400."

Appreciate your input!

cyboashu · Accepted Answer · 2016-07-22 03:00:18Z

0

Type 64 is for array. Also pass the address as parameter to the Range function to return a range.

Set rngPos = Range(Application.InputBox("Event Table will be size (" & UBound(arrayDest, 1) & _
", " & UBound(arrayDest, 2) & "). Choose the top left cell of desired location.", _
"Output Location", Type:=8).Address)

answered Jul 22, 2016 at 3:00

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chris neilsen Over a year ago

Application.InputBox( ... , Type:=8) returns a range reference. Imbedding that into Set rngPos = Range( InputBox( ... , Type:=8).Address) is pointless, just use Set rngPos = InputBox( ... , Type:=8)

Collectives™ on Stack Overflow

Excel VBA: Passing a variable sized two dimensional array to user selected range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related