Excel VBA - Assigning range to variant using .cells property error,

Question

I've the following code extract below,

Dim profileRange as Variant

profileRange = ActiveWorkbook.Worksheets("Scenario").Range(Cells(1, "C"), Cells(5, "C"))

Then I get the following in the watch expression

Watch Expression: profileRange
Value: Empty
Type: Variant/Empty

It's supposed to get the numbers assigned in that sheet..which goes form 1 to 5

Also, I get this when running the code

Error 1004:
Application or Object defined error

Can someone please help?

Scott Craner · Accepted Answer · 2016-01-30 04:19:58Z

1

Change:

profileRange = ActiveWorkbook.Worksheets("Scenario").Range(Cells(1, "C"), Cells(5, "C"))

to this:

With ActiveWorkbook.Worksheets("Scenario")
    profileRange = .Range(.Cells(1, "C"), .Cells(5, "C"))
End with

Cells() are range object and need to have parentage assigned. Without dictating parentage it is trying to use the active sheet, so the range parentage does not equate to the cells parentage.

By using the With block with the . allows consistent parentage throughout.

edited Jan 30, 2016 at 4:19

answered Jan 30, 2016 at 4:12

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lukieleetronic Over a year ago

Hey Scott, how do I assign this to the profileRange?

Scott Craner Over a year ago

@lukieleetronic sorry forgot the array. I fixed it. Please consider marking as correct.

Collectives™ on Stack Overflow

Excel VBA - Assigning range to variant using .cells property error,

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related