First, is Orch supposed to contain the values in that range? (I'm guessing
yes.)
I did this (and assigned some of the variables):
Option Explicit
Dim wbkObj As Workbook
Dim sht_Orch_Obj As Worksheet
Dim OrchRows As Integer
Dim Orch As Variant
'note the loss of ()'s
'some versions of excel will accept the ()'s
'some won't. So I leave it off.
'"dim orch() as Variant" will work in xl2002, though.
'set up some test values
Sub aaaa()
OrchRows = 12
Call Get_Orch
End Sub
Private Sub Get_Orch()
ReDim Orch(OrchRows, 14)
Dim RangeV As Range
Dim OrchRows_Plus As Integer
Set sht_Orch_Obj = ActiveSheet 'set it to something
OrchRows_Plus = OrchRows + 8
Set RangeV = sht_Orch_Obj.Range("A9:N" & OrchRows_Plus)
'RangeV is already a range, so you don't need to wrap it in range().
Orch = RangeV.Value
'but you could have used the .address if you wanted to.
Orch = sht_Orch_Obj.Range(RangeV.Address).Value
End Sub
athuggard wrote:
Hi all. I am having a problem defining a variable size range of cells
that I am trying to get into a array. Mulitple problems here. First
off, the range statement gives me a "run time error 1004", "application
or object defined error". Secondly, the method I am trying to use to
get a varible data set (range) into my array doesn't work. Is there a
better way?
Running Excel 2002 SP-2 on WinXP SP-1
Thanks in advance.
-Adam
Dim excelApp As Excel.Application
Dim wbkObj As Workbook
Dim sht_Orch_Obj As Worksheet
Dim OrchRows As Integer
Dim Orch() As Variant
..
Private Sub Get_Orch()
ReDim Orch(OrchRows, 14)
Dim RangeV As Range
Dim OrchRows_Plus As Integer
OrchRows_Plus = OrchRows + 8
Set RangeV = Range("A9:N" & OrchRows_Plus)
Orch = sht_Orch_Obj.Range(RangeV)
End Sub
---
Message posted from http://www.ExcelForum.com/
--
Dave Peterson