vba logic - so close but not quite there (adding names with vba)

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

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
 
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
 
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
 
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
 
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!
 
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?
 
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.
 
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.
 
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))
 
Glad you found that error.

I used column B, but didn't fix it correctly when I pasted.
 
Back
Top