MS Excel VBA Code - Issue with a Simple Array

[OilEconomist]

Thanks in advance for your assistance. How do I obtain a range of dates from Sheet2 and transfer them to Sheet1 where there is a blank cell before the date and one after the date. I have made an example where I have some sample code with the following data. I get the error "Run-time error '1004': Application-defined or object-defined error"

arRng = .Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Value2

Sheet2

Book2
	A
8	IMPORTANT DATES
9	2021-03-12, Fri
10	2021-06-04, Fri
11	2021-09-10, Fri
12	2021-12-03, Fri
13	2022-03-11, Fri
14	2022-06-03, Fri
15	2022-09-09, Fri
16	2022-12-02, Fri
17	2023-03-10, Fri
18	2023-06-02, Fri
19	2023-09-08, Fri
20	2023-12-01, Fri
Sheet2

Sheet1

Book2
	E
8	DATES ADJUSTED
9
10	2021-03-12, Fri
11
12	2021-06-04, Fri
13
14	2021-09-10, Fri
15
16	2021-12-03, Fri
17
18	2022-03-11, Fri
19
20	2022-06-03, Fri
21
22	2022-09-09, Fri
23
24	2022-12-02, Fri
25
26	2023-03-10, Fri
27
28	2023-06-02, Fri
29
30	2023-09-08, Fri
31
32	2023-12-01, Fri
33
Sheet1

I was trying to use an Array, which seems to the most efficient, but it would not let me store them with something like this:

Option Explicit
'***************************************************************************************************************
Sub myArrayIssue()

    Dim i As Long, CellPos As Long
    Dim FirstRow As Long, LastRow As Long, FirstCol As Long, LastCol As Long
    Dim arRng() As Variant

    'These values could change and I will find the first row, last row, etc. just simplified here
        FirstRow = 9
        LastRow = 20
        FirstCol = 1
        LastCol = 1

    'Store the array
     With Sheets("Sheet2") 'would like to avoid activating a sheet
        arRng = .Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol)).Value2
     End With
    
    'Output the values with spaces into "Sheet1" without activating it
     With Sheets("Sheet1")
        CellPos = 10
        For i = 1 To UBound(arRng)
            .Cells(CellPos, 5) = arRng(i, 1)
            CellPos = CellPos + 2
        Next i
     End With
   
  
End Sub

 

[cmowla]

You forgot to put a . in front of the two Cells( when storing it. So only when Sheet2 was the active sheet, would it store the range into the array. I also put a line of code in exchange for the LastRow = 20 to have it automatically find the last row for you. (Well, as long as the original list is in Column A.)

Option Explicit
'**********************************************
Sub myArrayIssue_Modified()

    Dim i As Long, CellPos As Long
    Dim FirstRow As Long, LastRow As Long, FirstCol As Long, LastCol As Long
    Dim arRng() As Variant

    'These values could change and I will find the first row, last row, etc. just simplified here
        FirstRow = 9
        LastRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row
        FirstCol = 1
        LastCol = 1

    'Store the array
     With Sheets("Sheet2") 'would like to avoid activating a sheet
        arRng = .Range(.Cells(FirstRow, FirstCol), .Cells(LastRow, LastCol)).Value2
     End With
  
    'Output the values with spaces into "Sheet1" without activating it
     With Sheets("Sheet1")
        CellPos = 10
        For i = 1 To UBound(arRng)
            .Cells(CellPos, 5) = arRng(i, 1)
            CellPos = CellPos + 2
        Next i
     End With
 
 
End Sub

 

[OilEconomist]

You forgot to put a . in front of the two Cells( when storing it. So only when Sheet2 was the active sheet, would it store the range into the array. I also put a line of code in exchange for the LastRow = 20 to have it automatically find the last row for you. (Well, as long as the original list is in Column A.)

Option Explicit
'**********************************************
Sub myArrayIssue_Modified()

    Dim i As Long, CellPos As Long
    Dim FirstRow As Long, LastRow As Long, FirstCol As Long, LastCol As Long
    Dim arRng() As Variant

    'These values could change and I will find the first row, last row, etc. just simplified here
        FirstRow = 9
        LastRow = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row
        FirstCol = 1
        LastCol = 1

    'Store the array
     With Sheets("Sheet2") 'would like to avoid activating a sheet
        arRng = .Range(.Cells(FirstRow, FirstCol), .Cells(LastRow, LastCol)).Value2
     End With
 
    'Output the values with spaces into "Sheet1" without activating it
     With Sheets("Sheet1")
        CellPos = 10
        For i = 1 To UBound(arRng)
            .Cells(CellPos, 5) = arRng(i, 1)
            CellPos = CellPos + 2
        Next i
     End With
 
 
End Sub

@cmowla Thanks! That did the trick. Also, thanks for setting up the last row calculation. Very kind of you for doing that extra step.​