0

This code prints the values of an array into a range, but for some odd reason only the first item of the array (array(1) if you will) is being pasted into every cell in the aforementioned range. This is the code itself:

    With ws2
        Dim arr5() As Variant
        Set clls = .Range(.Cells(2, 8), .Cells(lr(ws2, 1), 9))
        arr5 = clls.Value2
    End With
    
    lb1 = LBound(arr5, 1)
    lb2 = LBound(arr5, 2)
    ub1 = UBound(arr5, 1)
    lb2 = UBound(arr5, 2)

    Dim arr6() As Variant
    ReDim arr6(lb1 To ub1)
    
    With ws2
        For rows = 1 To lr(ws2, 1) - 1
            debe = arr5(rows, 1)
            habr = arr5(rows, 2)
            arr6(rows) = debe - habr
        Next rows
        Set clls = .Range(.Cells(2, 10), .Cells(lr(ws2, 1), 10))
            clls.Value = arr6
    End With

The lr(ws, col) udf takes ws as worksheet and col (column) as long and finds the index of the last row in said column within that worksheet using the End.XlUp.Row method. Find it's code below in case you wish to inspect it:

Public Function lr(ws As Worksheet, col As Long) As Long

    lr = ws.Cells(ws.rows.Count, col).End(xlUp).row
    
End Function

Lastly, this is the sort of data that the code is working with:

Data before the macro runs

Data after the macro runs

academic_dwarf
  • 23
  • 5
  • Try using `ws.Cells(2, 10).resize(Ubound(arr6), 1).Value = Application.Transpose(arr6)`. You declared `arr6` as 1D array, **which does not have rows**! It should be transposed to drop its content in a column (on rows). Then, no need to build the range to receive the dropped values in the complicated way you try. The above suggested solution works and it is more compact. I mean, replace `Set clls = .Range(.Cells(2, 10), .Cells(lr(ws2, 1), 10))` and `clls.Value = arr6` with what I suggested... – FaneDuru Jul 14 '22 at 12:16

1 Answers1

0

The fix was ReDim the array as a bidimensional array changing ReDim arr6(lb1 To ub1) to ReDim arr6(lb1 To ub1, 1 To 1) and arr6(rows, 1) = debe - habr to arr6(rows, 1) = debe - habr).

academic_dwarf
  • 23
  • 5