0

It seems this question also contains proper answer, Excel for some unfathomable reason won't execute it without error. So the question has changed a bit:

Why 1004?

Basically I want to use something like (This give me 1004):

Dim rngTmp As Range
For Each offer In SanitizedConstInfo("offers").keys()
    Set rngTmp = Sheets(offer).Range(Cells(1, 1), Cells(2, 2))
    ActiveWorkbook.Names.add name:=offer, RefersToR1C1:=rngTmp
    ActiveWorkbook.Names(offer).RefersToRange.Cells(1, 1) = offer
Next offer

offer is string containing some name (Yeah, I want to have both sheet and named range with same name - for now at least). Will have unknown number of those, so I just loop for each.

Q: How to add sheet information to RefersToR1C1, so that named range refers to certain sheet? (I know about 'Sheetname'!A1:A10 Syntax but want to do that with Sheet/Range/Cell objects if possible)

Community
  • 1
  • 1
przemo_li
  • 3,932
  • 4
  • 35
  • 60
  • I don't understand your question here... *`want to do that with Sheet/Range/Cell objects if possible`* can you better explain? –  Mar 24 '14 at 10:29
  • How to change that "Sheets(offer)" to make that code work. I'm not sure if I can use *just* Range() to select those cells, since for each name those will be on different sheet. – przemo_li Mar 24 '14 at 10:31
  • 1
    but now if your `offer` variable contains `Test` then your named range `Test` refers to `=Test!$A$1:$B$2` so I don't understand what you are trying to change here... –  Mar 24 '14 at 10:33
  • Hmm. Then I have right code but for some reason I get 1004 here. :\ I checked that Sheets with those names are present, and I do not know what else may cause that error. – przemo_li Mar 24 '14 at 10:35
  • can you add a MsgBoxes before that line and show yourself `offer` maybe it has an invalid name that can't be used as Named Range or Sheet Name...:? –  Mar 24 '14 at 10:37
  • I create Sheets before with those names. eg "testZlecenie3" – przemo_li Mar 24 '14 at 10:38
  • that seems like a valid name. Are you sure it's this line that actually throws an error? I can't reproduce this :/ When a sheet does not exist then I get a subscript out of range error but not 1004.. –  Mar 24 '14 at 10:40
  • Its exactly "Sheets(offer).Range(Cells(1, 1), Cells(2, 2))", I moved that code to "Set rngTmp = Sheets(offer).Range(Cells(1, 1), Cells(2, 2))" and I get 1004 here. And code above that was working when tested separately. – przemo_li Mar 24 '14 at 10:45

1 Answers1

1

It's because you aren't fully qualifying your ranges, you need to be explicit:

With Sheets(offer)
    Set rngTmp = .Range(.Cells(1, 1), .Cells(2, 2))
End With

Dots before Cells are important.

przemo_li
  • 3,932
  • 4
  • 35
  • 60
SWa
  • 4,343
  • 23
  • 40