PDA

View Full Version : Name a range with For



JDone
10-28-2007, 03:58 PM
I am trying to name a range depending on the user input
But it doesn't seem to name any range
My guess is that it is not recognizing the input, as it is captured correctly.

So the actual sheet has a bunch of names in a row, and i want to match the name from the inputbox to the name in the row and then name the range (data) below it.

Any ideas?


Name= InputBox("Please input a name")

With Worksheets("Names").Range("A1")
For i = 1 To 10
If .Offset(0, i) = " & Name & " Then
Range(.Offset(1, 0), .End(xlDown)).Name = "Name"
End If
Next
End With

Norie
10-28-2007, 04:34 PM
First thing I would suggest is not using Name for the variable.

XLGibbs
10-28-2007, 04:46 PM
Sub fooDefinename()
Dim strName As String, strRefers As String, rngLook As Range, rngFound As Range
Dim ws As Worksheet
Set ws = Sheets("Names")
Set rngLook = ws.Range(Cells(1, 1), Cells(65536, 1).End(xlUp))

strName = InputBox("enter a name")

'lets make sure the name is in the list...
Set rngFound = rngLook.Find(strName)

If Not rngFound Is Nothing Then 'if there is a match, add the reference
'define the range downward
Set rngFound = ws.Range(Cells(rngFound.Row, 1), Cells(rngLook.Rows.Count, 1))

strRefers = "Names!" & rngFound.Address

ActiveWorkbook.Names.Add Name:=strName, RefersTo:=strRefers

Else
MsgBox "name not in list'"
End If
End Sub








I think there is more to what you want to do here, so I won't add anymore...but this does what you describe precisely.