MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problem with named ranges

  Asked By: Jamil    Date: Feb 12    Category: MS Office    Views: 624

I have a worksheet with a block of data that I'm trying to apply a
number of named ranges to. The cells in row 1 all contain text that
I'd like to use as the name of the range. In the rows below there
are a variable number to cells containing other number or text.

What i'm trying to do is get code that will iterate along the
columns using the text in the first row to name the range of cells
below. This is the best i've some up with, but it keeps failing on
the RefersToR1C1 part with a run time error '1004'. Any ideas as to
where i've gone wrong?

Sub test()
'show how many columns there are
MsgBox WorksheetFunction.CountA(Rows(1))

For x = 1 To WorksheetFunction.CountA(Rows(1))
With ActiveWorkbook.Names
.Add _
Name:=Range("Sheet1!A1").Offset(0, x - 1).Value, _
RefersToR1C1:="=OFFSET(Sheet1!R2C" & x & ",0,0,CountA(Columns
(x) - 1,1)"
End With
Next x



4 Answers Found

Answer #1    Answered By: Lurleen Fischer     Answered On: Feb 12

Try this, it doesn't result in the dynamic ranges  you may be looking
for but it correctly deals with any gaps in columns and any gaps in
rows, which CountA doesn't:

Sub blah()
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
For x = 1 To lastcol
lastrow = Cells(Rows.Count, x).End(xlUp).Row
If Cells(1, x) <> "" Then
With ActiveWorkbook.Names
.Add Name:=Cells(1, x).Value, _
RefersTo:="=Sheet1!" & Range(Sheets("Sheet1").Cells(2, x), _
Sheets("Sheet1").Cells(lastrow, x)).Address
End With
End If
Next x
End Sub

Answer #2    Answered By: Helina Bonkob     Answered On: Feb 12

Thanks for this! I managed to modify it so that it had the desired
effect. I have another similar issue...

One of the named  ranges i've generated contains a number  of cells
that have embedded shape objects in them. What I can't figure out is
how to copy the objects from this named range  into another named

It works fine using Ctrl-C Ctrl-V on the keyboard, but using VBA

range("abc").value = range("xyz").value
range("abc").Copy Destination:=Range("xyz")

both fail to copy the objects. Amy i missing something obvious?

Answer #3    Answered By: Cara Lewis     Answered On: Feb 12

range("B10:F10").Copy Destination:=Range("B17")

worked, but seemed to depend on how much or which parts of the object
overlapped the cells  selected for copying. If the objects were
completey within the selection copying was fine. Control Toolbox
toolbar objects were not copied, however, Forms toolbar objects were.

Answer #4    Answered By: Luis Fischer     Answered On: Feb 12

i still can't get it working using named  ranges, but the following
seems to get the job done, so i guess it'll do!

Application.CutCopyMode = False

Didn't find what you were looking for? Find more on Problem with named ranges Or get search suggestion and latest updates.