Set an Array with a variable name VBA

Question

I am trying to use VBA to set an array based on a cell value in my workbook. I have an Array set on my data called THREEMO. For this month the value is Aug. I want to add a Sheet named Aug and then set an array for Columns B:C and name them Aug as well. I was able to add the sheet and name it but the array is not letting me use the variable to name the array. Here is what I have so far:

Dim WS As Worksheet
Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))
WS.Name = Range("THREEMO")

Dim stNewTab As String
stNewTab = Range("THREEMO")

'Set Arrays
Columns("B:C").Select
ActiveWorkbook.Names.Add Name:=Range("THREEMO"), RefersToR1C1:="=Range("THREEMO")!C2:C3"

I am getting the error on the "'Set Arrays" section. This is what it looks like when I record the macro, so I am just having trouble setting up RefersToR1C1 with the variable for the month:

ActiveWorkbook.Names.Add Name:="AUG", RefersToR1C1:="=Aug!C5:C6"

change RefersToR1C1:="=Range("THREEMO")!C2:C3" to RefersToR1C1:=Range("THREEMO") & "!C2:C3" — PartyHatPanda
– PartyHatPanda, Commented Sep 21, 2016 at 13:44

Siddharth Rout · Accepted Answer · 2016-09-21 17:32:27Z

1

Change

ActiveWorkbook.Names.Add Name:=Range("THREEMO"), RefersToR1C1:="=Range("THREEMO")!C2:C3"

to

ActiveWorkbook.Names.Add Name:=stNewTab, _
                         RefersToR1C1:="=" & stNewTab & "!C2:C3"

Note/Explanation:

Range("THREEMO") within quotes is considered as a string. Keep it outside the quotes. Better still use the variable stNewTab

The line

ActiveWorkbook.Names.Add Name:="AUG", RefersToR1C1:="=Aug!C5:C6"

can also be written as

ActiveWorkbook.Names.Add Name:="AUG", RefersToR1C1:="=" & "Aug" & "!C5:C6"

Now all you have to do is replace "Aug" with the variable.

edited Sep 21, 2016 at 17:32

answered Sep 21, 2016 at 13:45

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

4 Comments

Richard G Over a year ago

So I found out that the "!C5:C6" is transferring over just like that with the quotation marks. This is not allowing the array to function correctly, but when I remove them it breaks.

Siddharth Rout Over a year ago

Sorry I just noticed a typo.. Now try the code that I posted above.. You may have to refrsh the page...

Siddharth Rout Over a year ago

ActiveWorkbook.Names.Add Name:=stNewTab, RefersToR1C1:="=" & stNewTab & "!C2:C3" Try this. It will work now :)

Richard G Over a year ago

I was doing it wrong. Thank you very much you got me back on track.

Shai Rado · Accepted Answer · 2016-09-21 14:05:34Z

1

Another option, you can set up the Range like this:

Dim Rng As range
Set Rng = WS.Range("C5:C6")

And then, you can use the Names.Add command a little different

ActiveWorkbook.Names.Add Name:=stNewTab, RefersTo:=Rng

answered Sep 21, 2016 at 14:05

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

4 Comments

Shai Rado Over a year ago

@RichardGyou took off the V sign ? at least leave the upvote :)

Siddharth Rout Over a year ago

++ I upvoted your answer. Shai, one point though... Asking users to accept your answer or upvoting your answer is frowned upon in stackoverflow. No offence meant but it is considered "begging" which I am sure you do not want to do? ;)

Shai Rado Over a year ago

@SiddharthRout you're right (it's 5:00 AM here). it's just the past few days I've encountered too many answers of mine which were used later on with future posts, but the user never accepted them (a little frustrating)

Siddharth Rout Over a year ago

Yeah I understand that. I have been there ;)

Collectives™ on Stack Overflow

Set an Array with a variable name VBA

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related