0

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,"" "","""")"

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

Is this possible?

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?

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

Improve this question
3
  • Are you trying to (dynamically) change the name that is pointing to a fixed area of the sheet, or are you just trying to assign a name determined by the value of a cell at the time of assignment? Commented Dec 1, 2016 at 19:01
  • A formula cannot have side-effects. It takes input, computes a value, returns a result. The answer is no. Commented Dec 1, 2016 at 19:13
  • Thank you for helping! I am trying to assign a name determined by the value of a cell at the time of assignment Commented Dec 1, 2016 at 19:13

2 Answers 2

Reset to default
2

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

enter image description here

YowE3K Thanks for the help!

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This works perfectly! Thank you - I really appreciate you taking the time to help me!
@Haley YowE3K pointed out a problem. My latest update fixed it.
This still works, but adding onto it, how would I reference this range later? For example, if I need to clear cell contents, I am currently using selection, but is there another way I could refer to the range? I understand it is named after the code has run, but while it's running, how should I reference it?
Use Range("name") will reference a named range (e.g. Range("AddSection_test.1."). But while it is running I would set a variable to point to Selection. For instance Set Target = Selection.Cells. From there just use Target to reference the original Selection.
1

I believe what you are trying to do is:

Selection.Name = "AddSection_" &  Replace(Worksheets("Add Section").Range("D3").Value, " ", "")

or, setting it up to ensure that the range name has not yet been used, perhaps something like:

Dim myName As String
Dim maxSuffix As Long
Dim n As Name
myName = "AddSection_" & Replace(Worksheets("Add Section").Range("D3").Value, " ", "")
maxSuffix = 0
For Each n In Names
    If Left(n.Name, Len(myName)) = myName Then
        If IsNumeric(Mid(n.Name, Len(myName) + 1)) Then
            If CLng(Mid(n.Name, Len(myName) + 1)) > maxSuffix Then
                maxSuffix = CLng(Mid(n.Name, Len(myName) + 1))
            End If
        End If
    End If
Next
Selection.Name = myName & (maxSuffix + 1)

This only increments the count if the existing base name has been used before, i.e. AddSection_OilFurnace1, then AddSection_OilFurnace2, then maybe AddSection_GasFurnace1 - it doesn't go AddSection_OilFurnace1, AddSection_GasFurnace2, AddSection_OilFurnace3 - but maybe it is useful.

Improve this answer

4 Comments

Nice answer. I hope you don't mind I refactored your code for my answer.
@ThomasInzina - mine should ignore eggcarton when processing egg (due to carton... not being numeric) and should ignore egg when processing eggcarton (due to the first 9 characters of egg... not being eggcarton). I still have problems processing egg after egg456, but I doubt whether the user will ever be using anything ending with numeric values.
Wow you are amazing! Thank both you and YowE3K so much!
Wow...who would have thought that this would be so involved. It's a good thing that this is just a hobby. Anyway, I think I finally got it. Thanks again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.