VBA creating name range with complex string

artikox

New Member
Joined
Jan 31, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

Some videos you may like

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
Joined
Aug 4, 2017
Messages
1,059
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 4, 2017
Messages
1,059
Office Version
  1. 2016
Platform
  1. Windows
In a sum function you can use INDEX. In a VBA range not: Y = "AD" & Z & ":AD" & W
 

artikox

New Member
Joined
Jan 31, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank you for your answer mart37, but I'm still confusing :oops:
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,059
Office Version
  1. 2016
Platform
  1. Windows
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
 
Solution

artikox

New Member
Joined
Jan 31, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Yesssss Great MART37 finally it works!!!
Thank you so much
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top