If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
vba logic - so close but not quite there (adding names with vba)
Does anyone know how to fix the code below? It's not setting the ranges
correctly Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1).Value irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub Starting in bx25 TR30MNE V23:V61 TR30MPE W23:W61 TR30MSE X23:X61 TR30FNE Y23:Y61 TR30FPE Z23:Z61 TR30FSE AA23:AA61 TR30UNE AB23:AB61 TR30UPE AC23:AC61 TR30USE AD23:AD61 TR30MNE AE23:AE61 TR30MPE AF23:AF61 TR30MSE AG23:AG61 TR30FNE AH23:AH61 TR30FPE AI23:AI61 TR30FSE AJ23:AJ61 TR30UNE AK23:AK61 TR30UPE AL23:AL61 TR30USE AM23:AM61 |
#2
|
|||
|
|||
vba logic - so close but not quite there (adding names with vba)
Getting closer
Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:="=" & .Offset(irow, 1).Value irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub Using the same in the first column it is grabbing the wrong range (columns CT-Dk) What I have in the second column Termrates!V23:V61 Termrates!W23:W61 Termrates!X23:X61 Termrates!Y23:Y61 Termrates!Z23:Z61 Termrates!AA23:AA61 Termrates!AB23:AB61 Termrates!AC23:AC61 Termrates!AD23:AD61 Termrates!AE23:AE61 Termrates!AF23:AF61 Termrates!AG23:AG61 Termrates!AH23:AH61 Termrates!AI23:AI61 Termrates!AJ23:AJ61 Termrates!AK23:AK61 Termrates!AL23:AL61 Termrates!AM23:AM61 -- Wag more, bark less "Brad" wrote: Does anyone know how to fix the code below? It's not setting the ranges correctly Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1).Value irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub Starting in bx25 TR30MNE V23:V61 TR30MPE W23:W61 TR30MSE X23:X61 TR30FNE Y23:Y61 TR30FPE Z23:Z61 TR30FSE AA23:AA61 TR30UNE AB23:AB61 TR30UPE AC23:AC61 TR30USE AD23:AD61 TR30MNE AE23:AE61 TR30MPE AF23:AF61 TR30MSE AG23:AG61 TR30FNE AH23:AH61 TR30FPE AI23:AI61 TR30FSE AJ23:AJ61 TR30UNE AK23:AK61 TR30UPE AL23:AL61 TR30USE AM23:AM61 |
#3
|
|||
|
|||
vba logic - so close but not quite there (adding names with vba)
Refers to wants a range but you are passiing it the value of the cell Remove
the .Value Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1) 'Changed irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub -- HTH... Jim Thomlinson "Brad" wrote: Does anyone know how to fix the code below? It's not setting the ranges correctly Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1).Value irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub Starting in bx25 TR30MNE V23:V61 TR30MPE W23:W61 TR30MSE X23:X61 TR30FNE Y23:Y61 TR30FPE Z23:Z61 TR30FSE AA23:AA61 TR30UNE AB23:AB61 TR30UPE AC23:AC61 TR30USE AD23:AD61 TR30MNE AE23:AE61 TR30MPE AF23:AF61 TR30MSE AG23:AG61 TR30FNE AH23:AH61 TR30FPE AI23:AI61 TR30FSE AJ23:AJ61 TR30UNE AK23:AK61 TR30UPE AL23:AL61 TR30USE AM23:AM61 |
#4
|
|||
|
|||
vba logic - so close but not quite there (adding names with vb
Sorry It looks like you want the value of the cell to be the address of the
named range... Try this... Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= .range(.Offset(irow, 1).value) 'Changed irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Refers to wants a range but you are passiing it the value of the cell Remove the .Value Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1) 'Changed irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub -- HTH... Jim Thomlinson "Brad" wrote: Does anyone know how to fix the code below? It's not setting the ranges correctly Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1).Value irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub Starting in bx25 TR30MNE V23:V61 TR30MPE W23:W61 TR30MSE X23:X61 TR30FNE Y23:Y61 TR30FPE Z23:Z61 TR30FSE AA23:AA61 TR30UNE AB23:AB61 TR30UPE AC23:AC61 TR30USE AD23:AD61 TR30MNE AE23:AE61 TR30MPE AF23:AF61 TR30MSE AG23:AG61 TR30FNE AH23:AH61 TR30FPE AI23:AI61 TR30FSE AJ23:AJ61 TR30UNE AK23:AK61 TR30UPE AL23:AL61 TR30USE AM23:AM61 |
#5
|
|||
|
|||
vba logic - so close but not quite there (adding names with vba)
One more -- but first a few questions.
is ShtTerm the same as worksheets("TermRates")? I'm not sure. Second, when I'm creating these kinds of names, I think I'd want to keep them local to the sheet. I think it makes it easier -- when I move/copy the sheet to a different workbook, I don't have to worry about being nagged about names already existing in that different workbook. And I can use the same name in different worksheets in the same workbook -- this can be useful for lots of things. And finally, I'd want to know if the naming was successful. This code starts in BX25 and works until the last used cell in column BX (starting at the bottom and looking up). It may be equivalent to your code if the data is nice. Anyway... Option Explicit Sub Addnames1() Dim myCell As Range Dim myRng As Range Dim TestRng As Range With shtTerm Set myRng = .Range("Bx25", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell Set TestRng = Nothing On Error Resume Next Set TestRng = Application.Range(.Offset(0, 1).Value) On Error GoTo 0 If TestRng Is Nothing Then .Offset(0, 2).Value = "Not a range!" Else .Offset(0, 2).Value = "Ok" On Error Resume Next 'use one of the new .name lines. Comment/delete the other 'This first one is a workbook level name TestRng.Name = .Value 'this one is a worksheet level name TestRng.Name = "'" & TestRng.Parent.Name & "'!" & .Value If Err.Number 0 Then Err.Clear .Offset(0, 2).Value = "Invalid Name!" End If On Error GoTo 0 End If End With Next myCell End Sub And if you're working with names... Get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: NameManager.Zip from http://www.oaltd.co.uk/mvp It'll make your life lots easier! Brad wrote: Does anyone know how to fix the code below? It's not setting the ranges correctly Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1).Value irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub Starting in bx25 TR30MNE V23:V61 TR30MPE W23:W61 TR30MSE X23:X61 TR30FNE Y23:Y61 TR30FPE Z23:Z61 TR30FSE AA23:AA61 TR30UNE AB23:AB61 TR30UPE AC23:AC61 TR30USE AD23:AD61 TR30MNE AE23:AE61 TR30MPE AF23:AF61 TR30MSE AG23:AG61 TR30FNE AH23:AH61 TR30FPE AI23:AI61 TR30FSE AJ23:AJ61 TR30UNE AK23:AK61 TR30UPE AL23:AL61 TR30USE AM23:AM61 -- Dave Peterson |
#6
|
|||
|
|||
vba logic - so close but not quite there (adding names with vb
Jim,
Always glad to see when you reply to one of my e-mail. However this time your tip didn't work. Rather than using the information in the cells example V23:V61 is is using CT45:CT85 Oddly enough the row difference is 24 (one less that BX25) and the column difference is 22 which is the difference between (BX1 and CT - which is column V (22) So (my guess is with) with shtTerm.Range("bx25") - Do you agree? -- Wag more, bark less "Jim Thomlinson" wrote: Sorry It looks like you want the value of the cell to be the address of the named range... Try this... Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= .range(.Offset(irow, 1).value) 'Changed irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Refers to wants a range but you are passiing it the value of the cell Remove the .Value Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1) 'Changed irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub -- HTH... Jim Thomlinson "Brad" wrote: Does anyone know how to fix the code below? It's not setting the ranges correctly Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1).Value irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub Starting in bx25 TR30MNE V23:V61 TR30MPE W23:W61 TR30MSE X23:X61 TR30FNE Y23:Y61 TR30FPE Z23:Z61 TR30FSE AA23:AA61 TR30UNE AB23:AB61 TR30UPE AC23:AC61 TR30USE AD23:AD61 TR30MNE AE23:AE61 TR30MPE AF23:AF61 TR30MSE AG23:AG61 TR30FNE AH23:AH61 TR30FPE AI23:AI61 TR30FSE AJ23:AJ61 TR30UNE AK23:AK61 TR30UPE AL23:AL61 TR30USE AM23:AM61 |
#7
|
|||
|
|||
vba logic - so close but not quite there (adding names with vb
Dave,
Thanks for replying Yes, shtTerm is the same as worksheets("TermRates") I'm trying to add a range name and the range this it is associated with. I can't see how your code is doing this - what am I missing? I'll try do get the add-in you talked about by security is very tight where I work and I may not be able to obtain it. -- Wag more, bark less "Dave Peterson" wrote: One more -- but first a few questions. is ShtTerm the same as worksheets("TermRates")? I'm not sure. Second, when I'm creating these kinds of names, I think I'd want to keep them local to the sheet. I think it makes it easier -- when I move/copy the sheet to a different workbook, I don't have to worry about being nagged about names already existing in that different workbook. And I can use the same name in different worksheets in the same workbook -- this can be useful for lots of things. And finally, I'd want to know if the naming was successful. This code starts in BX25 and works until the last used cell in column BX (starting at the bottom and looking up). It may be equivalent to your code if the data is nice. Anyway... Option Explicit Sub Addnames1() Dim myCell As Range Dim myRng As Range Dim TestRng As Range With shtTerm Set myRng = .Range("Bx25", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell Set TestRng = Nothing On Error Resume Next Set TestRng = Application.Range(.Offset(0, 1).Value) On Error GoTo 0 If TestRng Is Nothing Then .Offset(0, 2).Value = "Not a range!" Else .Offset(0, 2).Value = "Ok" On Error Resume Next 'use one of the new .name lines. Comment/delete the other 'This first one is a workbook level name TestRng.Name = .Value 'this one is a worksheet level name TestRng.Name = "'" & TestRng.Parent.Name & "'!" & .Value If Err.Number 0 Then Err.Clear .Offset(0, 2).Value = "Invalid Name!" End If On Error GoTo 0 End If End With Next myCell End Sub And if you're working with names... Get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: NameManager.Zip from http://www.oaltd.co.uk/mvp It'll make your life lots easier! Brad wrote: Does anyone know how to fix the code below? It's not setting the ranges correctly Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1).Value irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub Starting in bx25 TR30MNE V23:V61 TR30MPE W23:W61 TR30MSE X23:X61 TR30FNE Y23:Y61 TR30FPE Z23:Z61 TR30FSE AA23:AA61 TR30UNE AB23:AB61 TR30UPE AC23:AC61 TR30USE AD23:AD61 TR30MNE AE23:AE61 TR30MPE AF23:AF61 TR30MSE AG23:AG61 TR30FNE AH23:AH61 TR30FPE AI23:AI61 TR30FSE AJ23:AJ61 TR30UNE AK23:AK61 TR30UPE AL23:AL61 TR30USE AM23:AM61 -- Dave Peterson . |
#8
|
|||
|
|||
vba logic - so close but not quite there (adding names with vb
What part of the code do want an explanation for?
The code checks to see if your entry in the adjacent cell can be a real range: TermRates!jjjjjj12:#$2 isn't valid. Then if it's not a valid address, it gives a warning in the next column (to the right of the address). If the range is valid, the code checks to see if the name can be used. Not all strings are valid for Names. If the name is invalid, then it gives a warning. If the name is valid, it either creates a local (worksheet level) name or a global (workbook level) name. Your choice. Brad wrote: Dave, Thanks for replying Yes, shtTerm is the same as worksheets("TermRates") I'm trying to add a range name and the range this it is associated with. I can't see how your code is doing this - what am I missing? I'll try do get the add-in you talked about by security is very tight where I work and I may not be able to obtain it. -- Wag more, bark less "Dave Peterson" wrote: One more -- but first a few questions. is ShtTerm the same as worksheets("TermRates")? I'm not sure. Second, when I'm creating these kinds of names, I think I'd want to keep them local to the sheet. I think it makes it easier -- when I move/copy the sheet to a different workbook, I don't have to worry about being nagged about names already existing in that different workbook. And I can use the same name in different worksheets in the same workbook -- this can be useful for lots of things. And finally, I'd want to know if the naming was successful. This code starts in BX25 and works until the last used cell in column BX (starting at the bottom and looking up). It may be equivalent to your code if the data is nice. Anyway... Option Explicit Sub Addnames1() Dim myCell As Range Dim myRng As Range Dim TestRng As Range With shtTerm Set myRng = .Range("Bx25", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell Set TestRng = Nothing On Error Resume Next Set TestRng = Application.Range(.Offset(0, 1).Value) On Error GoTo 0 If TestRng Is Nothing Then .Offset(0, 2).Value = "Not a range!" Else .Offset(0, 2).Value = "Ok" On Error Resume Next 'use one of the new .name lines. Comment/delete the other 'This first one is a workbook level name TestRng.Name = .Value 'this one is a worksheet level name TestRng.Name = "'" & TestRng.Parent.Name & "'!" & .Value If Err.Number 0 Then Err.Clear .Offset(0, 2).Value = "Invalid Name!" End If On Error GoTo 0 End If End With Next myCell End Sub And if you're working with names... Get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: NameManager.Zip from http://www.oaltd.co.uk/mvp It'll make your life lots easier! Brad wrote: Does anyone know how to fix the code below? It's not setting the ranges correctly Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1).Value irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub Starting in bx25 TR30MNE V23:V61 TR30MPE W23:W61 TR30MSE X23:X61 TR30FNE Y23:Y61 TR30FPE Z23:Z61 TR30FSE AA23:AA61 TR30UNE AB23:AB61 TR30UPE AC23:AC61 TR30USE AD23:AD61 TR30MNE AE23:AE61 TR30MPE AF23:AF61 TR30MSE AG23:AG61 TR30FNE AH23:AH61 TR30FPE AI23:AI61 TR30FSE AJ23:AJ61 TR30UNE AK23:AK61 TR30UPE AL23:AL61 TR30USE AM23:AM61 -- Dave Peterson . -- Dave Peterson |
#9
|
|||
|
|||
vba logic - so close but not quite there (adding names with vb
First of all thanks you
I was so focused that I had to have a "RefersTo", that I couldn't see how your code would work, but it now makes sense. For anyone else following this thread - the following code Set myRng = .Range("Bx25", .Cells(.Rows.Count, "B").End(xlUp)) that Dave had in his code should be Set myRng = .Range("Bx25", .Cells(.Rows.Count, "Bx").End(xlUp)) -- Wag more, bark less "Dave Peterson" wrote: What part of the code do want an explanation for? The code checks to see if your entry in the adjacent cell can be a real range: TermRates!jjjjjj12:#$2 isn't valid. Then if it's not a valid address, it gives a warning in the next column (to the right of the address). If the range is valid, the code checks to see if the name can be used. Not all strings are valid for Names. If the name is invalid, then it gives a warning. If the name is valid, it either creates a local (worksheet level) name or a global (workbook level) name. Your choice. Brad wrote: Dave, Thanks for replying Yes, shtTerm is the same as worksheets("TermRates") I'm trying to add a range name and the range this it is associated with. I can't see how your code is doing this - what am I missing? I'll try do get the add-in you talked about by security is very tight where I work and I may not be able to obtain it. -- Wag more, bark less "Dave Peterson" wrote: One more -- but first a few questions. is ShtTerm the same as worksheets("TermRates")? I'm not sure. Second, when I'm creating these kinds of names, I think I'd want to keep them local to the sheet. I think it makes it easier -- when I move/copy the sheet to a different workbook, I don't have to worry about being nagged about names already existing in that different workbook. And I can use the same name in different worksheets in the same workbook -- this can be useful for lots of things. And finally, I'd want to know if the naming was successful. This code starts in BX25 and works until the last used cell in column BX (starting at the bottom and looking up). It may be equivalent to your code if the data is nice. Anyway... Option Explicit Sub Addnames1() Dim myCell As Range Dim myRng As Range Dim TestRng As Range With shtTerm Set myRng = .Range("Bx25", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell Set TestRng = Nothing On Error Resume Next Set TestRng = Application.Range(.Offset(0, 1).Value) On Error GoTo 0 If TestRng Is Nothing Then .Offset(0, 2).Value = "Not a range!" Else .Offset(0, 2).Value = "Ok" On Error Resume Next 'use one of the new .name lines. Comment/delete the other 'This first one is a workbook level name TestRng.Name = .Value 'this one is a worksheet level name TestRng.Name = "'" & TestRng.Parent.Name & "'!" & .Value If Err.Number 0 Then Err.Clear .Offset(0, 2).Value = "Invalid Name!" End If On Error GoTo 0 End If End With Next myCell End Sub And if you're working with names... Get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: NameManager.Zip from http://www.oaltd.co.uk/mvp It'll make your life lots easier! Brad wrote: Does anyone know how to fix the code below? It's not setting the ranges correctly Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1).Value irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub Starting in bx25 TR30MNE V23:V61 TR30MPE W23:W61 TR30MSE X23:X61 TR30FNE Y23:Y61 TR30FPE Z23:Z61 TR30FSE AA23:AA61 TR30UNE AB23:AB61 TR30UPE AC23:AC61 TR30USE AD23:AD61 TR30MNE AE23:AE61 TR30MPE AF23:AF61 TR30MSE AG23:AG61 TR30FNE AH23:AH61 TR30FPE AI23:AI61 TR30FSE AJ23:AJ61 TR30UNE AK23:AK61 TR30UPE AL23:AL61 TR30USE AM23:AM61 -- Dave Peterson . -- Dave Peterson . |
#10
|
|||
|
|||
vba logic - so close but not quite there (adding names with vb
Glad you found that error.
I used column B, but didn't fix it correctly when I pasted. Brad wrote: First of all thanks you I was so focused that I had to have a "RefersTo", that I couldn't see how your code would work, but it now makes sense. For anyone else following this thread - the following code Set myRng = .Range("Bx25", .Cells(.Rows.Count, "B").End(xlUp)) that Dave had in his code should be Set myRng = .Range("Bx25", .Cells(.Rows.Count, "Bx").End(xlUp)) -- Wag more, bark less "Dave Peterson" wrote: What part of the code do want an explanation for? The code checks to see if your entry in the adjacent cell can be a real range: TermRates!jjjjjj12:#$2 isn't valid. Then if it's not a valid address, it gives a warning in the next column (to the right of the address). If the range is valid, the code checks to see if the name can be used. Not all strings are valid for Names. If the name is invalid, then it gives a warning. If the name is valid, it either creates a local (worksheet level) name or a global (workbook level) name. Your choice. Brad wrote: Dave, Thanks for replying Yes, shtTerm is the same as worksheets("TermRates") I'm trying to add a range name and the range this it is associated with. I can't see how your code is doing this - what am I missing? I'll try do get the add-in you talked about by security is very tight where I work and I may not be able to obtain it. -- Wag more, bark less "Dave Peterson" wrote: One more -- but first a few questions. is ShtTerm the same as worksheets("TermRates")? I'm not sure. Second, when I'm creating these kinds of names, I think I'd want to keep them local to the sheet. I think it makes it easier -- when I move/copy the sheet to a different workbook, I don't have to worry about being nagged about names already existing in that different workbook. And I can use the same name in different worksheets in the same workbook -- this can be useful for lots of things. And finally, I'd want to know if the naming was successful. This code starts in BX25 and works until the last used cell in column BX (starting at the bottom and looking up). It may be equivalent to your code if the data is nice. Anyway... Option Explicit Sub Addnames1() Dim myCell As Range Dim myRng As Range Dim TestRng As Range With shtTerm Set myRng = .Range("Bx25", .Cells(.Rows.Count, "B").End(xlUp)) End With For Each myCell In myRng.Cells With myCell Set TestRng = Nothing On Error Resume Next Set TestRng = Application.Range(.Offset(0, 1).Value) On Error GoTo 0 If TestRng Is Nothing Then .Offset(0, 2).Value = "Not a range!" Else .Offset(0, 2).Value = "Ok" On Error Resume Next 'use one of the new .name lines. Comment/delete the other 'This first one is a workbook level name TestRng.Name = .Value 'this one is a worksheet level name TestRng.Name = "'" & TestRng.Parent.Name & "'!" & .Value If Err.Number 0 Then Err.Clear .Offset(0, 2).Value = "Invalid Name!" End If On Error GoTo 0 End If End With Next myCell End Sub And if you're working with names... Get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: NameManager.Zip from http://www.oaltd.co.uk/mvp It'll make your life lots easier! Brad wrote: Does anyone know how to fix the code below? It's not setting the ranges correctly Sub Addnames1() Dim irow As Integer irow = 0 With shtTerm.Range("bx25") Do ActiveWorkbook.Names.Add _ Name:=.Offset(irow, 0), _ RefersTo:= _ .Offset(irow, 1).Value irow = irow + 1 Loop Until .Offset(irow, 0) = "" End With End Sub Starting in bx25 TR30MNE V23:V61 TR30MPE W23:W61 TR30MSE X23:X61 TR30FNE Y23:Y61 TR30FPE Z23:Z61 TR30FSE AA23:AA61 TR30UNE AB23:AB61 TR30UPE AC23:AC61 TR30USE AD23:AD61 TR30MNE AE23:AE61 TR30MPE AF23:AF61 TR30MSE AG23:AG61 TR30FNE AH23:AH61 TR30FPE AI23:AI61 TR30FSE AJ23:AJ61 TR30UNE AK23:AK61 TR30UPE AL23:AL61 TR30USE AM23:AM61 -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|