Excel VBA：我可以使用公式命名一个范围吗？ [英] Excel VBA: Can I name a range using a formula?

问题描述

我的目标是命名我刚刚粘贴的范围，这是我将来可以找到的独特之处。

My goal is to name my just-pasted range something unique to I can find it in the future.

复制和粘贴的范围来自一个下拉菜单，因此必须进行修改

The copied and pasted range comes from a drop-down menu, and thus must be modified

Selection.Name.Formula = "=""AddSection_""&SUBSTITUTE('Add Section'!D3,"" "","""")"

如果他们在D3的下拉菜单中选择油炉，那么该部分将被复制并粘贴。应该命名为AddSection_OilFurnace

If they select Oil Furnace in D3's drop down, then that section is copied and pasted. It should be named "AddSection_OilFurnace"

这是可能吗？

我真的会喜欢的是如果我可以有一个命名范围，根据之前存在的数量进行更新。例如，上面将是AddSection_OilFurnace1，下一部分将是AddSection_GasFurnace2等等。但我不知道如何或如果这是可能的哈哈。会是这样的：

What I would REALLY love is if I could have a named range that updates based on how many exist before it. For example, the above would be "AddSection_OilFurnace1" and the next section would be "AddSection_GasFurnace2" and so on. But I have no idea how or if that is possible haha. Would it be something like:

Worksheets("Add Section").ranges.count

这是可能的，如何进入我的命名公式？

Is that possible and how would it go into my naming formula?

m超新的VBA，所以谢谢你的任何和所有的帮助！

I'm super new to VBA so thank you for any and all help!

推荐答案

我认为YowE3K有正确的方法。我重构了他的代码，因为我不喜欢 Do Loop 。

I think YowE3K has the right approach. I refactored his code because I don't like Do Loop.

Sub AddName()

    Dim myNameBase As String
    Dim arr() As String
    Dim maxName As Long
    Dim n As Name
    myNameBase = "AddSection_" & Replace(Worksheets("Add Section").Range("D3").Value, " ", "")

    For Each n In Names
        If n.Name Like myNameBase & "*" Then
            If n.Name = myNameBase Then
                maxName = 1
            ElseIf n.Name Like myNameBase & ".*." Then
                arr = Split(n.Name, ".")
                If arr(UBound(arr) - 1) >= maxName Then maxName = arr(UBound(arr) - 1) + 1
            End If

        End If
    Next
    Selection.Name = myNameBase & IIf(maxName, "." & maxName & ".", "")

End Sub

YowE3K感谢您的帮助！

YowE3K Thanks for the help!

这篇关于Excel VBA：我可以使用公式命名一个范围吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！