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: