VBA creating name range with complex string

[artikox]

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)

Sub NameRangeAdd()
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)
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=X
End Sub
Could you please help me ?

 

[mart37]

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

 

[mart37]

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

 

[artikox]

Thank you for your answer mart37, but I'm still confusing
I'm sorry I did not understand your first answer,
in VBA the variable string can include whatsoever, even INDEX and COUNT because it's a string
Could you please suggest something?

 

[mart37]

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 & "))"
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=Y

 

[artikox]

Yesssss Great MART37 finally it works!!!
Thank you so much