Generating Array from Inputbox Range

Jadendo

Board Regular
Joined
May 11, 2005
Messages
83
I am trying to populate an array from an user defined range via an inputbox, but am having a little trouble.

Basically been using the below... trying to force into an array, but I think there is some type mismatch somewhere.

Set LastRange() = Application.InputBox(Prompt:="Prompt", _
Title:="Range Finder", Type:=8)

Any idea on how I can assign that range to an array. Thanks guys!!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

facethegod

Well-known Member
Joined
Aug 1, 2006
Messages
767
Jadendo, try this out

Code:
Option Explicit
Sub try()
Dim LastRange(), x, i As Variant
Dim cell As Range
Set x = Application.InputBox(Prompt:="Prompt", Title:="RangeFinder", Type:=8)
ReDim LastRange(1 To x.Rows.Count)
For Each cell In x
i = cell.Count + i
LastRange(i) = cell
Next
End Sub

HTH
 
Upvote 0

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,961
Office Version
  1. 365
Platform
  1. Windows
Perhaps:

Code:
Sub test()
Dim x As Range, t
Set x = Application.InputBox("Select Range", Type:=8)
ReDim t(1 To x.Rows.Count, 1 To x.Columns.Count)
t = x
End Sub
 
Upvote 0

Forum statistics

Threads
1,190,616
Messages
5,981,951
Members
439,745
Latest member
VBANewbieJohn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top