Range Objects + Names + Error 1004

[David]

I seem to be running into the 1004 error on what I thought would be
something simple. I have created 4 names
one of which is "Clothing", it refers to cells B4 to B8. I want to
simply place the arrData into each of the five cells.
I can't get past that line setting the range object. I thought
setting range objects was easy. The range is already named what is
causing this error? A piece of the offending code follows: Is there
some "rule of thumb" to follow when setting and using range objects, I
always seem to be getting thrown with them. Thanks David.

Public Sub ApplyData()

Dim i As Integer
Dim j As Integer
Dim rng As Range

Sheets("Master").Activate

Set rng = Range("Clothing") ' Can't get past this line
rng.Cells(1) = arrData(0, 0)
rng.Cells(2) = arrData(0, 1)
rng.Cells(3) = arrData(0, 2)
rng.Cells(4) = arrData(0, 3)
rng.Cells(5) = arrData(0, 4)

 

[Dave Peterson]

Maybe fully qualifying that range would help:

Set rng = Worksheets("Master").Range("Clothing")
(guessing that Clothing is on the Master worksheet)

You may want to double check your spelling of the name, too. Maybe it was a
typo.

 

[David]

Maybe fully qualifying that range would help:

Set rng = Worksheets("Master").Range("Clothing")
(guessing that Clothing is on the Master worksheet)

You may want to double check your spelling of the name, too.  Maybe it was a
typo.









--

Dave Peterson- Hide quoted text -

- 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]

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.


David wrote: