none
Array Mismatch RRS feed

  • Question

  • Hi

    I have application where I have data in column A of sheet 1. This can go from no data, 1 cell of data (in cell A1) or maybe up to a large number of entries - I can't really guess a number until run time. I have written the following code to capture the data into an array. Note - if there is no data I bypass the code.

    Dim rngtest As Range
    Dim deletetable () As Variant

    Set rngtest = Sheet1.Range("A1").CurrentRegion
    deletetable = rngtest

    If there is data in two or more cells it works fine; however, if there is data in cell A1 only (i.e. one entry) then it falls over with Type Mismatch (error 13); I am not sure why? I am trying to build an array which I process later in the application

    any thoughts please?

    thanks

    Peter

    Thursday, March 16, 2017 9:03 AM

Answers

  • Peter

    Try this

    Sub test()
    Dim Deletable() As Variant
    Dim lngArraySize As Long
    Dim lngCounter As Long

        lngArraySize = Sheet1.Range("A1").CurrentRegion.Cells.Count
        ReDim Deletable(1 To lngArraySize)

        For lngCounter = 1 To lngArraySize
            Deletable(lngCounter) = Range("A" & lngCounter).Value
        Next lngCounter

    End Sub

    Two points to note.  First, if you are sure that your array size will not be huge, you can declare lngArraySize as an Integer and save some memory.  Second, deciding the array size by using CurrentRegion means that you cannot have any data in cells adjacent to column A (i.e. incolumn B) on the same rows, or immediately below the last line in column A.

    Hope it helps

    Andy C

           

    • Marked as answer by py1 Thursday, March 16, 2017 10:30 AM
    Thursday, March 16, 2017 10:02 AM

All replies

  • Peter

    Try this

    Sub test()
    Dim Deletable() As Variant
    Dim lngArraySize As Long
    Dim lngCounter As Long

        lngArraySize = Sheet1.Range("A1").CurrentRegion.Cells.Count
        ReDim Deletable(1 To lngArraySize)

        For lngCounter = 1 To lngArraySize
            Deletable(lngCounter) = Range("A" & lngCounter).Value
        Next lngCounter

    End Sub

    Two points to note.  First, if you are sure that your array size will not be huge, you can declare lngArraySize as an Integer and save some memory.  Second, deciding the array size by using CurrentRegion means that you cannot have any data in cells adjacent to column A (i.e. incolumn B) on the same rows, or immediately below the last line in column A.

    Hope it helps

    Andy C

           

    • Marked as answer by py1 Thursday, March 16, 2017 10:30 AM
    Thursday, March 16, 2017 10:02 AM
  • Andy

    Thanks for this - I managed to find a workaround which is not dissimilar to your suggestion - but thanks for the input!

    kind regards,

    Peter

    Thursday, March 16, 2017 10:30 AM