Excel VBA - define named ranges

Question

For each cell with content in range C4:C53 of worksheet "Size Selection", I want to create a new named range, and the name of that range should be the same as the content of that cell.

Each of the named ranges should be formula driven, and has a variable that depends on the row of the cell.

I've tried coding this, but keep getting errors; would you please take a look and help me resolve the issue?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:F20000")) Is Nothing Then

    Dim i As Integer
    Dim range_name As String


    For i = 4 To 53

    If Sheets("Size Selection").Cells(i, 3) <> "" Then

    Range(range_name).Formula = "=OFFSET('Size Selection'!$F$" & i & ", 0, 0, 1, COUNT(IF('Size Selection'!$F$" & i & ":$AZ$" & i & "="", "", 1)))"

    Range(range_name).Name = Sheets("Size Selection").Cells(i, 3)

    End If

    Next

    End If

End Sub

Many thanks for your help!

range_name is an empty string every time. You need to populate it correctly. — Kapol
– Kapol, Commented Mar 13, 2014 at 12:32

John Bustos · Accepted Answer · 2014-03-13 17:05:35Z

1

Based upon your comments in the discussion below, here's what I think you're after:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:F20000")) Is Nothing Then

    Dim i As Integer
    Dim range_name As String


    For i = 4 To 53

      If Sheets("Size Selection").Cells(i, 3) <> "" Then

       ActiveWorkbook.Names.Add _
          Name:= Sheets("Size Selection").Cells(i, 3).Text, _
          RefersToR1C1:="=OFFSET('Size Selection'!$F$" & i & ", 0, 0, 1, COUNT(IF('Size Selection'!$F$" & i & ":$AZ$" & i & "="", "", 1)))"

      End If

    Next

    End If

End Sub

It might be wrong on the RefersToR1C1 part, but it should at least now make sense to you on how to proceed.

Hope this does the trick!!

edited Mar 13, 2014 at 17:05

answered Mar 13, 2014 at 12:45

John Bustos

19.7k18 gold badges102 silver badges162 bronze badges

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

8 Comments

jcv Over a year ago

Thanks John. I have implemented what you suggested, but instead of naming the range, this renames the cell to N/A and causes an error. Also, need to add sheets("Size Selection") in front of the Cells(i, 3). Can't quite figure out what's not working

John Bustos Over a year ago

And the first line - The setting of the formula - Does it create unique values for each cell? When naming cells, they have to be unique and follow certain conventions... Just checking on this first before delving any more deeply....

jcv Over a year ago

John, many thanks. I don't want the cell name to change. Essentially, I want excel to check cell C4 and if the cell has content (<>""), then create a range which is named according to the content in C4, and apply the formula to define the range. Then move to C5 and so on until we are at C53.

John Bustos Over a year ago

I'm afraid I don't understand... So, are you saying C4:C53 will contain the address of the range to name? And, if so, what name will that range get... Please clarify!!!

jcv Over a year ago

C4:C53 each have names, e.g. C4 is "cookie" and C5 is "cake". I want VBA to check C4; if the cell has content, create a named range called "cookie" and apply a formula to populate it. Then move to C5 to create a new named range called "cake" and repeat until you hit C53.

|

Collectives™ on Stack Overflow

Excel VBA - define named ranges

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related