Relative Macro to Name a cell the contents of that cell.

**HELPME13** · 07-12-2012, 12:32 PM

Basically using the "record Macro" function with relative references on i select my first cell, hit "Define Name" and it defaults to the cell contents, then I hit Ok, move right to next column, rinse and repeat until my row is done.

The problem is, when I go to the next row to run the macro, it won't name the cells anything. I believe it is trying to name these cells the same names as the row above it. How do I get Excel to understand that I want the cells named by their contents?

Thanks!

Here is the Code the record macro function produced. I'm not Microsoft Visual Basic proficient, please help!

Sub CELLNAMETest1()
'
' CELLNAMETest1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveWorkbook.Names.Add Name:="pre1a", RefersToR1C1:= _
        "='BidPackageSelection NEW '!R29C6"
    ActiveWorkbook.Names("pre1a").Comment = ""
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="pre1b", RefersToR1C1:= _
        "='BidPackageSelection NEW '!R29C7"
    ActiveWorkbook.Names("pre1b").Comment = ""
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="pre1c", RefersToR1C1:= _
        "='BidPackageSelection NEW '!R29C8"
    ActiveWorkbook.Names("pre1c").Comment = ""
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="pre1d", RefersToR1C1:= _
        "='BidPackageSelection NEW '!R29C9"
    ActiveWorkbook.Names("pre1d").Comment = ""
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="pre1e", RefersToR1C1:= _
        "='BidPackageSelection NEW '!R29C10"
    ActiveWorkbook.Names("pre1e").Comment = ""
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="pre1f", RefersToR1C1:= _
        "='BidPackageSelection NEW '!R29C11"
    ActiveWorkbook.Names("pre1f").Comment = ""
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="pre1g", RefersToR1C1:= _
        "='BidPackageSelection NEW '!R29C12"
    ActiveWorkbook.Names("pre1g").Comment = ""
End Sub

**protonLeah** · 07-15-2012, 02:54 AM

Option Explicit

Sub test()
    Dim Col As Long
    For Col = 7 To 12
        On Error Resume Next
        ActiveWorkbook.Names.Add _
            Name:=ActiveCell.Value, _
            RefersToR1C1:="='BidPackageSelection NEW'!R" & ActiveCell.Row & "C" & Col
        If Err.Number > 0 Then Debug.Print Err.Description
        Err.Clear
        ActiveCell.Offset(0, 1).Select
    Next Col
End Sub

Relative Macro to Name a cell the contents of that cell.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Relative Macro to Name a cell the contents of that cell.

Re: Relative Macro to Name a cell the contents of that cell.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions