CODEWITHSUNDEEP
excel-2007vbarange
David Gard
David Gard

Reputation: 12057

'Object variable or With block variable not set' error when setting a range in VBA

I have a function that creates a Pivot Table, but I am getting an error when I try to set a range that will be merged and have a title added to it.

In the below code, pivot_title_range is a 'String' variable, and is optional when calling the function. title_range is a 'Range' variable. Both lines that set the range (whether or not the users declares pivot_title_range) cause the same error.

If pivot_title_range = "" Then
    title_range = ActiveSheet.Range("B3:E4")
Else
    title_range = ActiveSheet.Range(pivot_title_range)
End If

Here is the error that I am getting -

Run-time error '91':
Object variable or With block variable not set

If required, here is a Pastebin of the full function - http://pastebin.com/L711jayc. The offending code starts on line 160.

Is anybody able to tell me what I am doing wrong? Thanks.

Upvotes: 3

Views: 8751

Answers (1)

Jook
Jook

Reputation: 4682

You need to use 

If pivot_title_range = "" Then
    Set title_range = ActiveSheet.Range("B3:E4")
Else
    set title_range = ActiveSheet.Range(pivot_title_range)
End If

Because you defined title_range as range - and this is an Object ;)

Upvotes: 7

Related Questions