Set Range Question
 

I need to display an arrray of results that are passed to a routine. I can
get the desired output with the three options I've posted (really two
different approaches), but I'd like to understand why I have to substract 1
from the OutputCol to get the output into the desired column. In other words,
if I don't substract 1 from OutputCol the output is displayed in column 30
instead of column 29.

I could also use some help with the resize methodology in Option 3; I'm
really poking around on this one.

Here are the options I've used:
Dim OutputRow As Long, OutputCol As Long
Dim DestRange As Range
Dim ThisSheet As String

ThisSheet = ActiveSheet.Name
OutputRow = 5
OutputCol = 29

'Option 1
Set DestRange = Range(Worksheets(ThisSheet).Cells(OutputRow, OutputCol - 1), _
Worksheets(ThisSheet).Cells(OutputRow + UBound(DailyAvgData,
1), _
OutputCol + UBound(DailyAvgData, 2) - 1))

OR

'Option 2 (essentially the same as Option 1)
With Worksheets(ThisSheet)
Set DestRange = .Range(.Cells(OutputRow, OutputCol - 1), _
.Cells(OutputRow + UBound(DailyAvgData, 1), _
OutputCol + UBound(DailyAvgData, 2) - 1))
End With

OR

' Option 3 (a variation of a post from Leo Hauser)
With Worksheets(ThisSheet)
Set DestRange = .Range(.Cells(OutputRow, OutputCol - 1), _
.Cells(OutputRow, OutputCol - 1)). _
Resize(UBound(DailyAvgData, 1), UBound(DailyAvgData, 2) + 1)
End With

' Output the array
DestRange.Value = DailyAvgData

Thanks in advance,
Raul

Set Range Question
 

Excel defaults to 0 (zero) for the first item in an array, so column A in an
array is 0 and column AD is 29. So to get the result to be column AC which
Excel sees as 28, you must subtract 1. Confused?

This from VBA Help:
You can use the Option Base statement at the top of a module to change the
default index of the first element from 0 to 1. In the following example, the
Option Base statement changes the index for the first element, and the Dim
statement declares the array variable curExpense with 365 elements.

Option Base 1
Dim curExpense(365) As Currency



"Raul" wrote:

I need to display an arrray of results that are passed to a routine. I can
get the desired output with the three options I've posted (really two
different approaches), but I'd like to understand why I have to substract 1
from the OutputCol to get the output into the desired column. In other words,
if I don't substract 1 from OutputCol the output is displayed in column 30
instead of column 29.

I could also use some help with the resize methodology in Option 3; I'm
really poking around on this one.

Here are the options I've used:
Dim OutputRow As Long, OutputCol As Long
Dim DestRange As Range
Dim ThisSheet As String

ThisSheet = ActiveSheet.Name
OutputRow = 5
OutputCol = 29

'Option 1
Set DestRange = Range(Worksheets(ThisSheet).Cells(OutputRow, OutputCol - 1), _
Worksheets(ThisSheet).Cells(OutputRow + UBound(DailyAvgData,
1), _
OutputCol + UBound(DailyAvgData, 2) - 1))

OR

'Option 2 (essentially the same as Option 1)
With Worksheets(ThisSheet)
Set DestRange = .Range(.Cells(OutputRow, OutputCol - 1), _
.Cells(OutputRow + UBound(DailyAvgData, 1), _
OutputCol + UBound(DailyAvgData, 2) - 1))
End With

OR

' Option 3 (a variation of a post from Leo Hauser)
With Worksheets(ThisSheet)
Set DestRange = .Range(.Cells(OutputRow, OutputCol - 1), _
.Cells(OutputRow, OutputCol - 1)). _
Resize(UBound(DailyAvgData, 1), UBound(DailyAvgData, 2) + 1)
End With

' Output the array
DestRange.Value = DailyAvgData

Thanks in advance,
Raul

Set Range Question
 

Thanks JLGWhiz!

Any thoughts about the syntax on the resize statement?

Thanks again,
Raul

"JLGWhiz" wrote:

Excel defaults to 0 (zero) for the first item in an array, so column A in an
array is 0 and column AD is 29. So to get the result to be column AC which
Excel sees as 28, you must subtract 1. Confused?

This from VBA Help:
You can use the Option Base statement at the top of a module to change the
default index of the first element from 0 to 1. In the following example, the
Option Base statement changes the index for the first element, and the Dim
statement declares the array variable curExpense with 365 elements.

Option Base 1
Dim curExpense(365) As Currency



"Raul" wrote:

I need to display an arrray of results that are passed to a routine. I can
get the desired output with the three options I've posted (really two
different approaches), but I'd like to understand why I have to substract 1
from the OutputCol to get the output into the desired column. In other words,
if I don't substract 1 from OutputCol the output is displayed in column 30
instead of column 29.

I could also use some help with the resize methodology in Option 3; I'm
really poking around on this one.

Here are the options I've used:
Dim OutputRow As Long, OutputCol As Long
Dim DestRange As Range
Dim ThisSheet As String

ThisSheet = ActiveSheet.Name
OutputRow = 5
OutputCol = 29

'Option 1
Set DestRange = Range(Worksheets(ThisSheet).Cells(OutputRow, OutputCol - 1), _
Worksheets(ThisSheet).Cells(OutputRow + UBound(DailyAvgData,
1), _
OutputCol + UBound(DailyAvgData, 2) - 1))

OR

'Option 2 (essentially the same as Option 1)
With Worksheets(ThisSheet)
Set DestRange = .Range(.Cells(OutputRow, OutputCol - 1), _
.Cells(OutputRow + UBound(DailyAvgData, 1), _
OutputCol + UBound(DailyAvgData, 2) - 1))
End With

OR

' Option 3 (a variation of a post from Leo Hauser)
With Worksheets(ThisSheet)
Set DestRange = .Range(.Cells(OutputRow, OutputCol - 1), _
.Cells(OutputRow, OutputCol - 1)). _
Resize(UBound(DailyAvgData, 1), UBound(DailyAvgData, 2) + 1)
End With

' Output the array
DestRange.Value = DailyAvgData

Thanks in advance,
Raul