Writing a particular column of a two-dimension array to a worksheet I had some great feedback on my code previously here and I was hoping that I could get some further help with writing a column of the array to the worksheet.

I have an example here where I write one column of my array to the worksheet with a loop, but I'm wondering if there wouldn't be a more efficient way to do this:

Option Explicit
Option Base 1

Public Sub arrayMatch()

    Dim firstArray As Variant
    With ThisWorkbook.Worksheets(1)
        firstArray = .Range("A1", .Range("A1").End(xlDown).End(xlToRight))
    End With
    ReDim Preserve firstArray(UBound(firstArray, 1), UBound(firstArray, 2) + 1)

    Dim secondArray As Variant
    With ThisWorkbook.Worksheets(2)
        secondArray = .Range("A1", .Range("A1").End(xlDown).End(xlToRight))
    End With

    Dim rowFirstArray As Long
    Dim rowSecondArray As Long
    For rowFirstArray = 1 To UBound(firstArray,1)   
        For rowSecondArray = 1 To UBound(secondArray,1)        
            If firstArray(rowFirstArray, 3) = secondArray(rowSecondArray, 1) Then            
                firstArray(rowFirstArray, 4) = secondArray(rowSecondArray, 2)            
                Exit For     
            End If    
        Next rowSecondArray
    Next rowFirstArray
    Erase secondArray

    For rowFirstArray = 1 To UBound(firstArray, 1)
        ThisWorkbook.Worksheets(1).Cells(rowFirstArray, 4).Value = firstArray(rowFirstArray, 4)
    Next rowFirstArray
    Erase firstArray

End Sub
I wouldn't use 

Option Base 1


It's completely unneeded, just change your loops to go from LBound to UBound. Remember, it's always better to stick with defaults otherwise you need to explain why you're doing it. Much easier -

For rowFirstArray = LBound(firstArray, 1) To UBound(firstArray, 1)




What's this for:

Dim previousSetting As Boolean
previousSetting = Application.DisplayAlerts
Application.DisplayAlerts = False
tempWS.Delete
Application.DisplayAlerts = previousSetting


You're turning it off no matter what anyway, there's no reason to store the setting. Why not just put it into an If block instead?

If Application.DisplayAlerts Then
    Application.DisplayAlerts = False
    tempWS.Delete
    Application.DisplayAlerts = True
    Else: tempWS.Delete
End If




I'm not sure what this is doing -

ReDim Preserve firstArray(UBound(firstArray, 1), UBound(firstArray, 2) + 1)


Usually you need to say 1 to UBound(...) for a dimension. But, you can't redimension the first dimension in a non- one dimensional array. So I'm not sure what is supposed to happen here.



Don't often see the Erase method

Erase secondArray


More commonly you set the array to nothing if you need to clean it up, but usually you won't need to clean it up.



Your With blocks aren't really needed

With ThisWorkbook.Worksheets(1)
    firstArray = .Range("A1", .Range("A1").End(xlDown).End(xlToRight))
End With


Can just be

firstArray = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown).End(xlToRight))


But I think what you mean is

firstArray = Sheet1.Range(Sheet1.Cells(1,1),Sheet1.Cells(lastRow, lastColumn))


What it's currently doing is 

Range("A1") to (Rows.Count, 1)


The .End(xlToRight) isn't doing anything excepting the width of column A.



Dim rowFirstArray As Long
Dim rowSecondArray As Long
For rowFirstArray = 1 To UBound(firstArray,1)   
    For rowSecondArray = 1 To UBound(secondArray,1)        
        If firstArray(rowFirstArray, 3) = secondArray(rowSecondArray, 1) Then            
            firstArray(rowFirstArray, 4) = secondArray(rowSecondArray, 2) 


Is only needed if your two arrays are different sizes. Are they different sizes?