Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Objects + Names + Error 1004
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Objects + Names + Error 1004
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Objects + Names + Error 1004
On Mar 15, 10:55*am, Dave Peterson wrote:
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 wrote: 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- 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Objects + Names + Error 1004
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: <<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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error '1004' - the specified value is out of range. | Excel Programming | |||
setting range().hidden=True causes range error 1004 | Excel Programming | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Run-time error '1004' running to excel objects at once | Excel Programming | |||
Category Names runtime error 1004 | Excel Programming |