VBA Range from String

Question

This is kind of silly, but I've been stuck for a while in this simple statement:

    Dim range1 as Range
    Dim mysheet as String
    Dim myrange as String

    mysheet = "Sheet1"
    range = "A1:A10"

range1 = Worksheets(mysheet).Range(myrange)

I've testing all the solutions that I've found on the internet as for example this, this and this, but nothing.

All the time it gives me errors: 1004 "Error defined by the application" or "object variable or with not set".

I have tried the following:

range1 = ThisWorkbook.Worksheets(mysheet).Range(myrange)

range1 = ActiveWorkbook.Worksheets(mysheet).Range(myrange)

range1 = Sheets(mysheet).Range(myrange) (and the combinations above)

range1 = Worksheets(mysheet).Range(Cells(1,1), Cells(1,10)) (and the combinations with This/Active workbook)

and

with This/ActiveWorkbook
range1 = .Worksheets(mysheet).Range(myrange)
end with

None have worked.

This is a REALLY silly thing, but I've been stuck for a while now :s

Can anyone help me?

Really thanks in advance.

Best regards,

assylias · Accepted Answer · 2012-05-01 20:25:45Z

33

You need to use Set to assign objects:

Set range1 = Worksheets(mysheet).Range(myrange)

answered May 1, 2012 at 20:25

assylias

330k84 gold badges680 silver badges806 bronze badges

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

1 Comment

Christian Vielma Over a year ago

Really thanks... I knew it was really simply, but I was blind.

noodlegoat · Accepted Answer · 2023-05-06 12:34:14Z

-1

I get that you were stuck and I offer a slight stuckness-variant:

rngStr = corrSht.Range("T" & 63 + iterCnt).Address               
*** Set strRng = Range(rngStr)***

So sure, **** use Set to have string 'become' a range... But that didn't work until I added "Address" to the range string in the preceding line. So hovvering the cursor over rngStr showed the range rather than the range content. I find VBA very cheeky/irritatio like this. Wish I had a better overview of the "why" as opposed to the "how to" sometimes....

answered May 6, 2023 at 12:34

noodlegoat

11 bronze badge

Collectives™ on Stack Overflow

VBA Range from String

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related