Creating an range object error

Question

So i've read a ton of posts on stack about this and for some reason I can't understand what my problem is. I'm new to excel VBA so it's possible it's very obvious so I apologize. Trying to create a range object using a variable column and row_number. (this isn't the actual project, but it's a lot less convoluted to create a simple example than my real project.) I get a Run-time error '424' Object required when I try and compile. Any ideas?

Sub test()
    Dim testObj As Range
    row_number = 3
    dataCol = "O"
    Set testObj = (dataCol & row_number)


End Sub

mielk · Accepted Answer · 2015-11-18 12:34:26Z

2

Below is how you can get the reference to the range:

Sub test()
    Dim testObj As Range

    row_number = 3
    dataCol = "O"

    Set testObj = ActiveSheet.Range(dataCol & row_number)


End Sub

This will create the reference to the cell O3 in the currently active worksheet.

If you need the reference to the range from other worksheet you can do it like that:

Set testObj = Worksheets("sheet_name").Range(dataCol & row_number)

edited Nov 18, 2015 at 12:34

answered Nov 18, 2015 at 12:32

mielk

3,94014 silver badges20 bronze badges

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

1 Comment

StormsEdge Over a year ago

Figured it out. Thank you Gents! Appreciated!

user4039065 · Accepted Answer · 2015-11-18 12:32:55Z

2

You cannot simply construct a string and expect it to be a valid Range object. However, you can use that string to create one.

Set testObj = Range(dataCol & row_number)

This could equate to the INDIRECT function on a worksheet turing a text string into a valid cell reference.

answered Nov 18, 2015 at 12:32

user4039065

1 Comment

user1016274 Over a year ago

Don't skip references, they're useful and make your posts outstanding.

Collectives™ on Stack Overflow

Creating an range object error

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related