CODEWITHSUNDEEP
vbaexcelrange
PBD10017
PBD10017

Reputation: 1091

Return the name of a range.

I have a bunch of range names (mostly single cells) in a worksheet. When I change the cell named "Grade" I want its value to appear in another range called "GrdSrchSttng" (6 x 1 range) as the last value. However to do that I need to know that the particular cell named "Grade" changed. The solution to this question does not work.

I tried this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isIn As Boolean
Dim i As Integer
Dim r As Range


Set r = ActiveSheet.Range("GrdSrchSttng")

    If Target.Name.Name = "Search!Grade" Then ' <== this si the line I have the issue with
        Select Case Target.Value
            Case "All"
                r.ClearContents
                r.Cells(1, 1).Value = "All"
            Case ""
                r.ClearContents
                Target.Value = "All"
                r.Cells(1, 1).Value = "All"
            Case Else
                If r(1, 1).Value = "All" Then
                    r.ClearContents
                End If

                i = 1
                Do While r(i, 1).Value <> ""
                    If r(i, 1).Value = Target.Value Then
                        isIn = True
                    End If
                    i = i + 1
                Loop

                If Not isIn Then
                    r(i, 1).Value = Target.Value
                End If
        End Select
    End If
End Sub

Upvotes: 0

Views: 2620

Answers (1)

WGS
WGS

Reputation: 14169

Personally, using Target.Name.Name is a bit tricky. I prefer using a workaround for this instead, as it achieves basically the same result. Unless you specifically want to track the name of the named range, I suggest doing something like follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    Whatever = Range("Grade").Address
    If Target.Address = Whatever Then
        Range("GrdSrchSttng").Cells(1, 6).Value = Target.Value
    End If
End Sub

Screenshots:

Set-up:

enter image description here

Result when Grade is edited:

enter image description here

Let us know if this is amenable or otherwise. :)

Upvotes: 1

Related Questions