重命名命名范围 [英] Renaming named ranges

问题描述

我有一本工作簿，其中有许多要重命名的命名范围.我有一个包含旧名称和新名称的电子表格.

I have a workbook with many named ranges to rename. I have a spreadsheet with the old names and the new names.

这有效:

Dim strOldName As String
Dim strNewName As String            

strOldName = rngNamedRanges.Cells(1, 6).Value2
strNewName = strOldName & "_Renamed"

With ActiveWorkbook.Names(strOldName)
    .Name = strNewName
End With

这不是:

Dim strOldName As String
Dim strNewName As String            

strOldName = rngNamedRanges.Cells(1, 6).Value2
strNewName = CStr(rngNamedRanges.Cells(1, 8).Value2)

With ActiveWorkbook.Names(strOldName)
    .Name = strNewName
End With

很明显，分配strNewName时我做错了.

Clearly, I'm doing something wrong assigning strNewName.

我也尝试过使用.text，.value并修剪字符串，所有这些都具有相同的非结果.

I have also tried using .text, .value, and trimming the string, all with the same non-result.

无效代码不会产生错误.只是无法更改名称.

The non-working code does not produce an error. It just fails to change the name.

rngNamedRanges.Cells(1,6)是指包含纯文本的单元格.
rngNamedRanges.Cells(1,8)是指包含CONCATENATE公式的单元格，该公式根据其他列中包含的其他几条信息来创建新的范围名称.

rngNamedRanges.Cells(1,6) refers to a cell containing straight text.
rngNamedRanges.Cells(1,8) refers to a cell containing a CONCATENATE formula which creates the new range name based on several other pieces of info contained in other columns.

推荐答案

感谢您的输入！我仍然不明白为什么我举的第一个例子能奏效而第二个例子却不能奏效.尽管如此，以下代码似乎仍然有效.对于片段的格式不正确，我深表歉意.

Thanks for the input, all! I still don't understand why the first example I gave worked and the second one did not. Nonetheless, the following code appears to be working. I apologize for poor formatting of the snippet.

Dim rngNamedRanges As Range
Dim strOldName As String
Dim strNewName As String
Dim strRefersTo As String

    Set rngNamedRanges = ActiveWorkbook.Worksheets("Named Ranges").Range("A2:K909")

i = 1
Do Until [CONDITION] = ""
    strOldName = CStr(Trim(rngNamedRanges.Cells(i, 6).Value2))
    strNewName = CStr(Trim(rngNamedRanges.Cells(i, 8).Value2))

            strRefersTo = ActiveWorkbook.Names(strOldName).RefersTo

                'Update all the formulas to use the new name.
                For Each ws In Worksheets
                    If ws.Name <> "Named Ranges" Then
                        ws.Cells.Replace What:=strOldName, Replacement:=strNewName, LookAt _
                        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False
                    End If
                Next

            'Delete old name and replace with the new one
            ActiveWorkbook.Names(strOldName).Delete
            ActiveWorkbook.Names.Add strNewName, strRefersTo

        End If

    strOldName = ""
    strNewName = ""

    i = i + 1
Loop

这篇关于重命名命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！