Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

Range Objects + Names + Error 1004

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
David - 15 Mar 2008 00:48 GMT
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)
  .
  .
  .
End sub

As Always Much appreciated!
Dave Peterson - 15 Mar 2008 00:55 GMT
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.

> I seem to be running into the 1004 error on what I thought would be
> something simple.  I have created 4 names
[quoted text clipped - 26 lines]
>
> As Always Much appreciated!

Signature

Dave Peterson

David - 15 Mar 2008 02:08 GMT
> 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

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.