CODEWITHSUNDEEP
arraysvbaexcel
Chris
Chris

Reputation: 145

Array variable not holding value after sheet is deleted

Can someone explain why this happens? I have an array variable Loans() that stores several short strings that I need to use later for formatting a pivot table. The values get stored from the "Fund List" sheet. Then the sheet gets deleted, but I still need to use the values. Excel returns an object required error when I try to use the Loans() variable after the sheet is deleted. I have found the way around this is to not delete the sheet, but does anyone know why this is? The relevant code is below.

Sheets("Fund List").Select

    ' some code

Dim Loans() As Variant
Dim index As Integer
If LN > 0 Then
    ReDim Loans(LN - 1)
    index = 0
    For i = 1 To LastRow
        If Range("H" & i).Value = 3 Then
            Set Loans(index) = Range("A" & i)
            index = index + 1
        End If
    Next i
End If

    ' some more code

Sheets("Fund List").Delete

    ' more code

Set objField = objTable.PivotFields("Fund")
If LN > 0 Then
    For index = 0 To UBound(Loans)
        ' I get an object required error on the following line:
        LNFund = Loans(index)
        objField.PivotItems(LNFund).Visible = False
    Next index
End If

Upvotes: 2

Views: 57

Answers (1)

user4039065
user4039065

Reputation:

You are setting the variant array elements to the Range object.

Set Loans(index) = Range("A" & i)

Assign the Range.Value or Range.Value2 property instead.

Loans(index) = Range("A" & i).Value2

Note not Set.

Upvotes: 6

Related Questions