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?
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
With-statement? If not.Cellswouldn't "know" where it belongs ... Furthermore it is best practice to reference the workbook as well - to be on the safe side in case there are multiple workbooks open.