VBA Application/object defined error when outputting array to .Cell declared range

user_16449438 0
Viewed 68

I have an issue where I get the 'application-defined or object defined error' when trying to output an array into a range.

 Worksheets("Log").Range(.Cells(MnthCount + 4, 6), .Cells(MnthCount + 4, MnthDayCount + 5)) = FrstLtr

That is the code, the array FrstLtr is 31 values in length. MnthCount is 2, MnthDayCount is 31. There should be enough space in the range to output the array? What is the issue here?

transpose range vba arrays
1 Answers

Copy Array Values to Worksheet

A Quick Fix

With Worksheets("Log")
    .Range(.Cells(MnthCount + 4, 6), _
        .Cells(MnthCount + 4, MnthDayCount + 5)).Value = FrstLtr
End With

Understand This

Dim cCount As Long
cCount = (MnthDayCount + 5) - (6) + (1) ' 5 - 6 + 1 = 0
cCount = MnthDayCount

The Resize Connection

Worksheets("Log").Cells(MnthCount + 4, 6).Resize(, MnthDayCount).Value = FrstLtr
Worksheets("Log").Cells(MnthCount + 4, 6).Resize(, cCount).Value = FrstLtr

The Array Resize

With Worksheets("Log").Cells(MnthCount + 4, 6)
    .Resize(, UBound(FstrLtr) + 1).Value = FrstLtr ' 1D zero-based
    .Resize(, UBound(FstrLtr)).Value = FrstLtr ' 1D one-based
    .Resize(, UBound(FstrLtr, 2) + 1).Value = FrstLtr ' 2D zero-based
    .Resize(, UBound(FstrLtr, 2)).Value = FrstLtr ' 2D one-based
End With

A Common Scenario

Dim wb As Workbook: Set wb = ThisWorkbook

Dim dws As Worksheet: Set dws = wb.Worksheets("Log")

With dws.Cells(MnthCount + 4, 6)
    .Resize(, cCount).Value = FrstLtr
End With
user_98140690
Related