CODEWITHSUNDEEP
vbaruntime-error
JSRevenge
JSRevenge

Reputation: 53

Runtime error 424: Object Required

I made a huge mistake trying to bullet-proof something and accidentally saved over working code and don't know what I did wrong!

Here's the pertinent code:

Option Base 1
Dim userRange() as Range
Dim sheetCount as Integer

... (Code to determine "sheetCount" as defined by user here)...

ReDim userRange(sheetCount)
Dim r As Integer
For r = 1 To sheetCount
    Sheets(r).Select
    Debug.Print VBA.TypeName(userRange)
    Debug.Print VBA.TypeName(userRange())
    Debug.Print VBA.TypeName(userRange(r))
    Set userRange(r) = Application.InputBox( _
        Prompt:="Select a range to import.", _
        Title:="Select range # " & r, _
        Default:=currentsheet.Range("A1").Address, _
        Type:=8)

The line that throws the error is Set userRange(r)... Why won't VBA recognize userRange(r) as an object? The Immediate window prints:

Object()
Object()
Nothing

Please help! Thanks in advance!

JSR

Upvotes: 0

Views: 1476

Answers (1)

A. Webb
A. Webb

Reputation: 26446

The problem is not with userRange. You have no variable named currentSheet. Since you used Sheets(r).Select, you likely meant the object ActiveSheet instead. I recommend always using Option Explicit.

Upvotes: 0

Related Questions