VBA，Excel 2010 - 重命名命名范围 [英] VBA, Excel 2010 - renaming named ranges

问题描述

我有一个有很多命名范围的工作簿，我需要重命名它们。我有旧名字和新名字的spreadhseet。

这样做：

  Dim strOldName As String 
 Dim strNewName As String 
 
 strOldName = rngNamedRanges.Cells（1，6）.Value2 
 strNewName = strOldName& _Renamed
 
与ActiveWorkbook.Names（strOldName）
 .Name = strNewName 
结束
  

这不是：

  Dim strOldName As String 
 Dim strNewName As String 
 
 strOldName = rngNamedRanges.Cells（1，6）.Value2 
 strNewName = CStr（rngNamedRanges.Cells（1，8）.Value2）
 
使用ActiveWorkbook .Names（strOldName）
 .Name = strNewName 
结束
  

显然，我正在为我分配strNewName做错事。 。 。
我也尝试使用.text，.value和修剪字符串，全部具有相同的非结果。

非工作代码不会产生错误。它只是不能更改名称。

想法？谢谢！

**添加信息**
rngNamedRanges.Cells（1,6）是指包含直文本的单元格。
rngNamedRanges.Cells（1,8）是指包含CONCATENATE公式的单元格，它根据其他列中包含的其他几个信息创建新的范围名称。

解决方案

重命名永远是一种痛苦。尝试以下操作：

  Sub Rename（）
 
 StrOld =MyRange1
 StrNew = StrOld& _Renamed
 
 Range（StrOld）.Name = StrNew 
 With ThisWorkbook 
 .Names（StrOld）.Delete 
 End with 
 
结束子
  

循环取决于你。 :)让我们知道这是否有帮助。

I have a workbook with many named ranges, and I need to rename them. I have spreadhseet with the old names and the new names.

This works:

    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

This does not:

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

Clearly, I'm doing something wrong with how I'm assigning strNewName . . . 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.

Ideas? Thanks!

** Adding info ** 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.

解决方案

Renaming is always a pain. Try the following:

Sub Rename()

    StrOld = "MyRange1"
    StrNew = StrOld & "_Renamed"

    Range(StrOld).Name = StrNew
    With ThisWorkbook
        .Names(StrOld).Delete
    End With

End Sub

Looping is up to you. :) Let us know if this helps.

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