Create cell name from contents of another cell

Gatito

New Member
Joined
Mar 20, 2019
Messages
3
I'm looking for a way to create cell names dynamically. I can find more complex answers to more complex questions, but not this one.

For example: I'm trying to name cell B1 based on the contents of cell A1. And so on: the name of each cell in column B is the contents of each cell immediately to its left (in column A). For example, if A1 contains 'Bananas' then cell B1 takes the name 'Bananas'.

I can see how this can be done in a static fashion - but the key here is that I need a dynamic method. i.e. if the contents of A1 change to 'Apples' then cell B1 is now named 'Apples'.

A wrinkle is that I'm using Excel 2011 (Mac). I will upgrade if need be.
 

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
521
Place this first code in the sheet code you wish for it to work on.{Right click the sheet, view code, and paste it in}

Code:
Private Sub Worksheet_Change(ByVal Target As Range)    Dim Rng As Range, LastRow As Long


LastRow = Cells(Rows.Count, "A").End(xlUp).Row


    Set Rng = Range("A1:A" & LastRow)
    
    If Not Application.Intersect(Rng, Range(Target.Address)) _
           Is Nothing Then
                Call DelAndRenameRanges
    End If
End Sub
Place the following code in a normal module

Code:
Option Explicit

Sub DelAndRenameRanges()


Dim LastRow As Double, DefName As Name, stName As Variant


stName = ActiveSheet.Name


For Each DefName In Application.ActiveWorkbook.Names
    If InStr(1, DefName, stName) Then
        DefName.Delete
    End If
Next


LastRow = Cells(Rows.Count, "A").End(xlUp).Row


ActiveSheet.Range("A1:B" & LastRow).CreateNames Left:=True


End Sub
I cannot attest to if it will work with excel for Mac 2011, but it does in my copy of excel for Mac 2016
 
Last edited:

Gatito

New Member
Joined
Mar 20, 2019
Messages
3
Thank you! I've pasted into sheet code and a normal module respectively. Not doing anything for me yet in Excel 2011, but I'll persevere. Snappy response greatly appreciated. I'll let you know how I get on.
 

Gatito

New Member
Joined
Mar 20, 2019
Messages
3
Well, it works perfectly. I'll eventually figure out what I did wrong (I have barely dabbled with VB/macros) but with the answer in hand I am now off to the races - and mighty grateful. Thank you, thank you, thank you.
 

Forum statistics

Threads
1,085,253
Messages
5,382,591
Members
401,796
Latest member
Ginger12

Some videos you may like

This Week's Hot Topics

Top