> Maybe fully qualifying that range would help:
>
[quoted text clipped - 40 lines]
>
> - Show quoted text -
Thanks for your answer, unfortunately I'm still getting the error. I
'm starting to think there is something wrong with the names procedure
that I used. It is:
Sheets("Master").Activate
Range("B4").Select
Set rng = Range(ActiveCell.Address, "B27")
For i = 0 To rng.Rows.Count - 1
rng.Cells(i + 1).Select
If ActiveCell.Value = "Black" Then ' each time it
encounters "Black" (down the column), the cell to the left is what I
want the name to be. (Createnames ' 'doesn't work).
strName = ActiveCell.Offset(0, -1).Value
strAdd1 = ActiveCell.Address
strAdd2 = ActiveCell.Offset(4, 0).Address
strRange = strAdd1 & ":" & strAdd2
strSheet = "Master!"
ThisWorkbook.Names.Add Name:=strSheet & strName,
RefersTo:=strRange
End If
Next i
MsgBox "Names Created", vbInformation
Another interesting but frustrating thing is that the names box (on
the left side of the toolbar in the Excel GUI), doesn't show any of
the names.
This has excellerated my aging process!!
Thanks
Dave Peterson - 15 Mar 2008 03:00 GMT
Record a macro when you name a range and you'll see the syntax for the
.names.add statement. Your names are refering to strings--not ranges.
If you look at Insert|Name|Define and select one of those Names, you'll see that
it refers to something like:
="$B$7:$B$11"
Instead of something like:
=Master!$B$7:$B$11
========
You could modify your code to do this:
Thisworkbook.Names.Add Name:=strsheet & strName, _
RefersTo:="=" & strsheet & strRange
But you could accomplish the same thing without selecting and keeping track of
addresses.
But just to make sure, is this what you're doing?
Look at B4:B27 in the Master worksheet.
If you see Black, then use the value in column A to name that cell (5 rows deep
by 1 column wide).
So if this was in A7: ASDF7
and this was in B7: Black
Then b7:b11 would be named: ASDF7
If that's what you're doing, then this worked ok for me:
Option Explicit
Sub testme02()
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Set wks = Worksheets("Master")
With wks
Set myRng = .Range("b4:B27")
'or maybe to go until you run out of data in column B:
'Set myRng = .Range("b4", .Cells(.Rows.Count, "B").End(xlUp))
End With
For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase("Black") Then
myCell.Resize(5, 1).Name _
= "'" & wks.Name & "'!" & myCell.Offset(0, 1).Value
End If
Next myCell
MsgBox "Names Created", vbInformation
End Sub
Since you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:
You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp
You'll find it very valuable.
<<snipped>>
> Thanks for your answer, unfortunately I'm still getting the error. I
> 'm starting to think there is something wrong with the names procedure
[quoted text clipped - 26 lines]
> This has excellerated my aging process!!
> Thanks

Signature
Dave Peterson