CODEWITHSUNDEEP
vbaexcel
Kayote
Kayote

Reputation: 15617

Excel VBA - Dynamic Range obj referencing

Im doing what seems like a simple dynamic range. However, Im getting the following error:

"Error: Set method of range class failed" VBA doesnt like the dropdownRange obj & throws the above error on the second last line of code below. I am using this range to be used in validation dropdown list further down the list.

Dim mainTab As Object   'Tab 'main'
Dim tranTypeSize As Integer 'Length of entries for transaction type
Dim dropdownRange As Range 'Dynamic range for dropdown
Set mainTab = Sheets("Main")

With Sheets("Misc")
    tranTypeSize = .Cells(Rows.Count, 1).End(xlUp).Row
    Set dropdownRange = .Range("A1:A" & tranTypeSize)
    dropdownRange.Select
End With

Upvotes: 0

Views: 169

Answers (1)

John Coleman
John Coleman

Reputation: 51998

I assume that you mean Select method of range class failed. If so, the problem is that Sheet("Misc") isn't active.

To fix it, put .Select as the first line of the With block.

Upvotes: 1

Related Questions