Naming ranges with a twist

  • Thread starter Thread starter ryan.fitzpatrick3
  • Start date Start date
R

ryan.fitzpatrick3

Is there code to name a range? For example, I would want numbers 8103
in A:A to be called Ryan. They are all spread out in the column not
sorted together. (this is how the database pulls) I have a database
that keeps adding to itself, and more 8103's will get added so I would
like the code to recognize that the new added 8103 numbers should go
into that named range. Is this possible?

Ryan
 
Doesn't sound very practical. Perhaps if you describe what you ultimately
want to use the range for we can provide a workable solution.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
Ryan,

Try this:

Sub test()
Dim i As Range
Dim iEnd As Long
Dim rng As Range
Dim rngRyan As String

iEnd = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(iEnd, 1))

rngRyan = ""
For Each i In rng
If i = 8103 Then
rngRyan = i.Address & "," & rngRyan
End If
Next i

rngRyan = Mid$(rngRyan, 1, Len(rngRyan) - 1)

ActiveWorkbook.Names.Add _
Name:="Ryan", RefersToLocal:="=" & rngRyan

End Sub
 
Ryan,

Try this:

Sub test()
Dim i As Range
Dim iEnd As Long
Dim rng As Range
Dim rngRyan As String

iEnd = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(iEnd, 1))

rngRyan = ""
For Each i In rng
If i = 8103 Then
rngRyan = i.Address & "," & rngRyan
End If
Next i

rngRyan = Mid$(rngRyan, 1, Len(rngRyan) - 1)

ActiveWorkbook.Names.Add _
Name:="Ryan", RefersToLocal:="=" & rngRyan

End Sub
Click to expand...

Sorry didn't work. It's ok, i'm not entirely sure this is (naming
ranges) what I want. I'm having trouble with my workbook and I don't
know how to do in function or vba code.

Ryan
 
I agree with Ron that this doesn't sound like an approach that I would use.

Maybe using data|filter|autofilter and working on the visible cells would be
better (or maybe not).

Maybe sorting the data would work???

But if you want, maybe you could tie into the Worksheet_Change event. This
fires when a change is made to the worksheet by typing. (Not sure if it fits in
with how you import your database data.)

Anyway, if you want to try, rightclick on the worksheet tab and select view
code.

Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim FoundCell As Range
Dim FirstAddress As String
Dim myNameStr As String
Dim myStr As String

If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub
End If

myStr = "8103"

myNameStr = "Ryan"

On Error Resume Next 'in case it doesn't exist
Me.Names(myNameStr).Delete
On Error GoTo 0

With Me.Range("A:A")
Set FoundCell = .Cells.Find(what:=myStr, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then

Else
FirstAddress = FoundCell.Address
Do
If myRng Is Nothing Then
Set myRng = FoundCell
Else
Set myRng = Union(myRng, FoundCell)
End If

Set FoundCell = .FindNext(after:=FoundCell)

If FoundCell.Address = FirstAddress Then
'at the top again
Exit Do
End If
Loop
If myRng Is Nothing Then
'this shouldn't happen
Else
myRng.Name = "'" & Me.Name & "'!" & myNameStr
End If

End If
End With

End Sub

Then make a change to column A and try selecting the resulting Ryan name (via
edit|goto in xl2003).
 
The code I provided works, but only if you have less than 15 or so
occurances. So it isnt too practical, as Ron said.
 
Back
Top