Read range to array
 

Hi! I have a range. I want to loop through the range and place the values
from the cells into an array. However i have some trouble with the code. I
write:

Set rngCorr = ThisWorkbook.Sheets("Counterparty").Range("FactorC orrRange")
NoR = rngCorr.Rows.Count
ReDim dblCorrArray(NoR)
'here are the different correlations for the factos set
For i = 0 To NoR
dblCorrArray(i) = Range("FactorCorrRange").Offset(i, 0).Value
Next

I ger error 13 incompatible types. How shall i write the code? pls help!

Read range to array
 

Dim vaFCR As Variant
vaFCR = ThisWorkbook.Sheets("Counterparty").Range("FactorC orrRange").Value

This code will load your FactorCorrRange into a 2d array.
Use UBound(vaFCR,1) and UBound(vaFCR,2) to get the upper end points for each
of the dimensions. Both dimensions will begin at 1, not 0 (so be careful,
since this is different than the norm when dimensioning arrays, depending on
the "option base #" statement)

If anyone can point to a good way to make this a 1d array (other than making
a single cell reference and then using .offset to load each cell individually
to the array element in a loop)... That is what I was looking for when I
loaded the newsgroup this morning.


--
Regards,
John


"Arne Hegefors" wrote:

Hi! I have a range. I want to loop through the range and place the values
from the cells into an array. However i have some trouble with the code. I
write:

Set rngCorr = ThisWorkbook.Sheets("Counterparty").Range("FactorC orrRange")
NoR = rngCorr.Rows.Count
ReDim dblCorrArray(NoR)
'here are the different correlations for the factos set
For i = 0 To NoR
dblCorrArray(i) = Range("FactorCorrRange").Offset(i, 0).Value
Next

I ger error 13 incompatible types. How shall i write the code? pls help!

Read range to array
 

For a single column Range
v = Application.Transpose(Range("G1:G10").Value)

v is a 1d array.

If the range were Horizonal (single row range), then you would need

v =Application.Transpose(Application.Transpose(Range ("A1:M1").Value))

--
Regards,
Tom Ogilvy

"John Keith" wrote:


Dim vaFCR As Variant
vaFCR = ThisWorkbook.Sheets("Counterparty").Range("FactorC orrRange").Value

This code will load your FactorCorrRange into a 2d array.
Use UBound(vaFCR,1) and UBound(vaFCR,2) to get the upper end points for each
of the dimensions. Both dimensions will begin at 1, not 0 (so be careful,
since this is different than the norm when dimensioning arrays, depending on
the "option base #" statement)

If anyone can point to a good way to make this a 1d array (other than making
a single cell reference and then using .offset to load each cell individually
to the array element in a loop)... That is what I was looking for when I
loaded the newsgroup this morning.


--
Regards,
John


"Arne Hegefors" wrote:

Hi! I have a range. I want to loop through the range and place the values
from the cells into an array. However i have some trouble with the code. I
write:

Set rngCorr = ThisWorkbook.Sheets("Counterparty").Range("FactorC orrRange")
NoR = rngCorr.Rows.Count
ReDim dblCorrArray(NoR)
'here are the different correlations for the factos set
For i = 0 To NoR
dblCorrArray(i) = Range("FactorCorrRange").Offset(i, 0).Value
Next

I ger error 13 incompatible types. How shall i write the code? pls help!