I am testing different ways to use and write arrays. I can't figure out why when I write my array to a range it give me all 0's in the first column and the corrrect number in the second. The array is not 2D so i'm not sure why it is producing 2 columns worth of data. When I use a For Loop it works like want it to. Does "range.value = array" has some tricks to it?

Sub Pct_Chg()

Dim RowNum As Long

Dim i As Long

Dim InMdlArray() As Double

Dim InTtlArray() As Double

Dim PctOffArray() As Double

RowNum = ActiveSheet.UsedRange.rows.Count - 1

ReDim InMdlArray(1 To RowNum, 1)

ReDim InTtlArray(1 To RowNum, 1)

ReDim PctOffArray(1 To RowNum, 1)

For i = 1 To RowNum

InMdlArray(i, 1) = Cells(i + 1, 5)

InTtlArray(i, 1) = Cells(i + 1, 6)

Next i

For i = 1 To RowNum

On Error Resume Next

InAcct = InTtlArray(i, 1)

InModel = InMdlArray(i, 1)

PctOffArray(i, 1) = pct_off(InAcct, InModel)

Next

Range("j2").Resize(RowNum, 2) = PctOffArray

/*** If I use .Resize(Rownum, 1) it gives me a column full of 0's ***/

For i = 1 To RowNum

Cells(i + 1, 12).Value = PctOffArray(i, 1)

Next

End Sub

Function pct_off(InAcct, InModel)

pct_off = (((InAcct) / (InModel)) - 1)

End Function

Sub Pct_Chg()

Dim RowNum As Long

Dim i As Long

Dim InMdlArray() As Double

Dim InTtlArray() As Double

Dim PctOffArray() As Double

RowNum = ActiveSheet.UsedRange.rows.Count - 1

ReDim InMdlArray(1 To RowNum, 1)

ReDim InTtlArray(1 To RowNum, 1)

ReDim PctOffArray(1 To RowNum, 1)

For i = 1 To RowNum

InMdlArray(i, 1) = Cells(i + 1, 5)

InTtlArray(i, 1) = Cells(i + 1, 6)

Next i

For i = 1 To RowNum

On Error Resume Next

InAcct = InTtlArray(i, 1)

InModel = InMdlArray(i, 1)

PctOffArray(i, 1) = pct_off(InAcct, InModel)

Next

Range("j2").Resize(RowNum, 2) = PctOffArray

/*** If I use .Resize(Rownum, 1) it gives me a column full of 0's ***/

For i = 1 To RowNum

Cells(i + 1, 12).Value = PctOffArray(i, 1)

Next

End Sub

Function pct_off(InAcct, InModel)

pct_off = (((InAcct) / (InModel)) - 1)

End Function

Last edited: