Error Initializing Array Type Mismatch

Question

Attempting to utilize two arrays to iterate through a data transfer process by copy and pasting cells from one sheet into a newly created one. The code below is merely responsible for copy and pasting the correct data in the correct order from one sheet to the newly created one. I'm receiving a type mismatch when attempting to initialize the arrays. It occurs on the first array, but I haven't gotten to the second array to test that yet, so it could be wrong as well.

Things to Note: 1) firmLocationColumn is of type long. 2) All the data stored in said arrays are meant to represent column numbers. They are out of order so I needed to store them in the array in the proper order so that it's easier to iterate through them rather than writing the same information over and over again.

Let me know if I missed anything that needs to be explained and i'll edit my question:

Private Sub GetSpecificTradeDetails(ByVal masterListRow As Long, ByVal firmLocationColumn As Long, ByVal newExcelConfirmSheet As Worksheet, ByVal newExcelConfirmSheetLastRow As Long)

    Dim tradesMasterListColumnIndexArray() As Long
    Dim newExcelConfirmColumnIndexArray() As Long
    Dim arrayIndexCounter As Long

    'Sets array of columns for loop iteration through data sheet
    tradesMasterListColumnIndexArray() = [1,4,firmLocationColumn,(firmLocationColumn - 1),(firmLocationColumn + 3),15,16,10,11,8,19,18,17,(firmLocationColumn + 4),9,6,2]
    newExcelConfirmColumnIndexArray() = [1,2,3,4,5,7,8,9,10,11,12,13,14,15,16,17,18]

    Select Case firmLocationColumn

        Case 25

            'Sets confirm direction to "BUY"
            newExcelConfirmSheet.Cells((newExcelConfirmSheetLastRow + 1), 6) = "BUY"

        Case 27

            'Sets confirm direction to "SELL"
            newExcelConfirmSheet.Cells((newExcelConfirmSheetLastRow + 1), 6) = "SELL"

    End Select

    'Transfers trade details between the masterlist and the newly created confirm sheet
    With TradesMasterSheet

        For arrayIndexCounter = 0 To 17

            .Cells(masterListRow, tradesMasterListColumnIndexArray(arrayIndexCounter)).Copy _
                Destination:=newExcelConfirmSheet.Cells((newExcelConfirmSheetLastRow + 1), newExcelConfirmColumnIndexArray(arrayIndexCounter))

        Next

    End With


End Sub

John Coleman · Accepted Answer · 2016-04-27 13:34:56Z

3

VBA doesn't support initialization of arrays by array literals. It does, however, have an Array() function:

Dim tradesMasterListColumnIndexArray As Variant
Dim newExcelConfirmColumnIndexArray As variant

tradesMasterListColumnIndexArray = Array(1,4,firmLocationColumn,(firmLocationColumn - 1),(firmLocationColumn + 3),15,16,10,11,8,19,18,17,(firmLocationColumn + 4),9,6,2)
newExcelConfirmColumnIndexArray = Array(1,2,3,4,5,7,8,9,10,11,12,13,14,15,16,17,18)

answered Apr 27, 2016 at 13:34

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StormsEdge Over a year ago

Thanks very much for this john. I didn't realize you were unable to initialize arrays by array literals. That fixed the problem. Thanks very much. Soon as I am able to I will select your answer as the appropriate one.

Collectives™ on Stack Overflow

Error Initializing Array Type Mismatch

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related