Variables and range problems
 

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/

Variables and range problems
 

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

Variables and range problems
 

Thanks, Dave.

Looks like I was just being stupid.

Happy Holidays

--
Message posted from http://www.ExcelForum.com