VBA assigning cell references function

Question

I need a function that would assign cell references (through an Array with CTRL+SHIFT+ENTER) to a worksheet. The problem is: that it works well unless a referenced cell is empty which gives #VALUE ERROR to ALL the cells part of the array. This is the function that I use

Function GeneratePackingList(PackageTable As Range)
Dim bRow As Byte, bCol as Byte
Dim asResult()
ReDim asResult(1 To 25, 1 To 11)

With PackageTable
    For bRow = 1 To .Rows.Count
        For bCol = 1 To 11
            Select Case bCol
                'Rearranging the columns order
                Case 1 To 6:    Set asResult(bRow, bCol) = .Cells(bRow, bCol + 1)
                Case 7:         Set asResult(bRow, bCol) = .Cells(bRow, 1)
                Case 8 To 11:   Set asResult(bRow, bCol) = .Cells(bRow, bCol)
            End Select
        Next bCol
    Next bRow
End With
GeneratePackingList = asResult
End Function

So basically if one of those cells from PackageTable is empty, i get the #VALUE Error and all is lost. should i change variable declaration (variant, range or else ??) is there any other more efficient way to assign cell references through a function?

chris neilsen chris neilsen · Accepted Answer · 2011-11-13T23:44:08

Using Set in your case clauses is causing arResult to be an array of Ranges. This is not necassary and is causeing your problem. Just Delete the Sets

            Select Case bCol
                'Rearranging the columns order
                Case 1 To 6:     asResult(bRow, bCol) = .Cells(bRow, bCol + 1)
                Case 7:          asResult(bRow, bCol) = .Cells(bRow, 1)
                Case 8 To 11:    asResult(bRow, bCol) = .Cells(bRow, bCol)
            End Select

VBA assigning cell references function

1 Answers