CODEWITHSUNDEEP
excelvba
Learner_VBA
Learner_VBA

Reputation: 23

When asking for a Range input : object variable or with block variable not set in excel vba

I want user to input the cell Range

Dim FromRang, ToRang As Range

'Ask the user to input the cell Range in which to search for the sub strings
FromRang = Application.InputBox(Prompt:="Enter Search Range From:", Type:=8)
ToRang = Application.InputBox(Prompt:="Enter Search Range To:", Type:=8)

But this is giving me an error: "object variable or with block variable not set in excel vba"

Upvotes: 0

Views: 1239

Answers (1)

K_B
K_B

Reputation: 3678

Objects such as a Range can only be assigned in VBA by using Set. So as mehow mentioned you should use:

Set FromRang = Application.InputBox(Prompt:="Enter Search Range From:", Type:=8)

Furthermore dimensioning multiple variables requires you to state the type for every variable separately:

Dim FromRang As Range, ToRang As Range

Otherwise the first one will be unspecified and by default this results in it becoming of the type Variant which could lead to undesired behaviour.

Upvotes: 6

Related Questions