# VBA creating name range with complex string

#### artikox

##### New Member
I'm new to VBA. I'm attempting to create a Name Range but i get runtime error 1004 Application - defined or object defined error on the line Set X = Worksheets("Sheet1").Range(Y)

Dim X As Range
Dim Z As Integer
Dim RangeName As String
Dim Y As String
Z = Worksheets("Sheet1").Cells(1, 41).Value + 32
RangeName = "ValuesX"
Y = "Sheet1!\$AD\$" & Z & ":INDEX"Sheet1!\$AD\$" & Z & ":\$BK\$" & Z & ";COUNT"Sheet1!\$AD\$" & Z + 1 & ":\$BK\$" & Z + 1 & "))"
Set X = Worksheets("Sheet1").Range(Y)
End Sub

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### mart37

##### Well-known Member
In a range you don't refer to the sheet. The object for the range is the sheet. Worksheets("Sheet1").Range(Y)

#### mart37

##### Well-known Member
In a sum function you can use INDEX. In a VBA range not: Y = "AD" & Z & ":AD" & W

#### artikox

##### New Member
in VBA the variable string can include whatsoever, even INDEX and COUNT because it's a string

#### mart37

##### Well-known Member
Yes, you can put everything in a string.
But you need a string wich refers to a range: Set X = Worksheets("Sheet1").Range(Y)
The string Y refers not to a range.
If Y is a range:
W = Application.Evaluate("=INDEX(Sheet1!AD" & Z & ":BK" & Z & ",COUNT(Sheet1!AD" & Z + 1 & ":BK" & Z + 1 & "))")
Y = "AD" & Z & ":BK"& W
If Y = a formula:
Y = "Sheet1!\$AD\$" & Z & ":INDEX(Sheet1!\$AD\$" & Z & ":\$BK\$" & Z & ",COUNT(Sheet1!\$AD\$" & Z + 1 & ":\$BK\$" & Z + 1 & "))"

#### artikox

##### New Member
Yesssss Great MART37 finally it works!!!
Thank you so much

Replies
3
Views
80
Replies
4
Views
97
Replies
4
Views
40
Replies
43
Views
567
Replies
4
Views
22

1,127,529
Messages
5,625,340
Members
416,096
Latest member
forevans

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back