Solved

making dynamic named ranges from unique entries in first row rather than first column  of a table in excel

Posted on 2011-02-26
26
244 Views
Last Modified: 2012-05-11
I tried to get the logic behind the macro that was kindly made for me to make named ranges from unique items in the first column of a table(ID: 24458962), but cannot make it work.
I figured I just needed to invert the rows fro the columns, but that does not work!
I would appreciate it for the macro to work with unique entries in the first row from the macro that was written for the unique entries in the first column so that I can see where the simple interchange of row for columns does not work!
Thanks!

Option Explicit
Option Base 1

Sub MakeNames()

Dim arrUnique() 'create array of unique values in column A
Dim lngLastRow As Long 'find the row # of the last occupied cell in column A
Dim lngItem As Long 'represents items in the array
Dim lngRow As Long 'row # for loop
Dim lngLastCol As Long 'last occupied column in row 1
Dim lngFirst As Long '1st row # of the array item found in column A
Dim lngLast As Long 'last row # of the array item found in column A

lngLastRow = Range("A" & Rows.Count).End(xlUp).Row

For lngRow = 2 To lngLastRow
    If Application.CountIf(Range("A2:A" & lngRow), Range("A" & lngRow)) = 1 Then
        lngItem = lngItem + 1
        ReDim Preserve arrUnique(lngItem)
        arrUnique(lngItem) = Range("A" & lngRow).Value
    End If
Next lngRow

lngLastCol = Range("IV1").End(xlToLeft).Column

For lngItem = LBound(arrUnique) To UBound(arrUnique)
    lngFirst = Range("A:A").Find(arrUnique(lngItem), , , xlWhole, xlRows, xlNext).Row
    lngLast = Range("A:A").Find(arrUnique(lngItem), , , xlWhole, xlRows, xlPrevious).Row
    Names.Add "Range_" & arrUnique(lngItem), Range(Cells(lngFirst, 1), Cells(lngLast, lngLastCol))
Next lngItem

End Sub

Open in new window

0
Comment
Question by:daniques
  • 13
  • 13
26 Comments
 

Author Comment

by:daniques
ID: 34987090
I have an additional problem; I have tried re running the example sheet provided in 2009 for the thread on the range naming using the unique entries in the first column, and I am now getting error91 with the following message:
"object variable or With block variable not set".
The macro ran perfectly when I first used it, and I do not recall having changed any setting on my computer. I am baffled as I do not have any hints as to what is happening.
I downloaded th example file back from the site to see whether I had changed the file, but I got the same error message 91. The example file is downloadable from the site when referring to the ID: 24533908.
Thanks for all the help!
Danièle
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34987170
Danièle,

Please upload the relevant file.

Thanks

Patrick
0
 

Author Comment

by:daniques
ID: 34987204
Sorry about that!
Here is the example file,
Many thanks!
Danièle


Example.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34987879
Danièle,

Thanks for the file. Can you confirm what you wanting to achieve. Are you wanting, for example, the range C2:C6 to be named 'a' and so on?

Patrick
0
 

Author Comment

by:daniques
ID: 34989573
Hi Patrick,
Yes, being so late, I did not take the time to edit the file for clarity. I really am sorry.
I have now added the file with this message and on the first page, I have described what I was aiming for, viz naming ranges based on the first row entries. The file also has the ranges naming for the unique entries in the first column with the attached macro for completeness.
I think it might be the way I word the request but I have seen people writing macros and when entering notations such as "XlUp", there appears on the screen a drop down menu for them to choose. The help in VBA has been very unfruitful in that I wish to turn this on to be able at least to judge whether my replacing columns by rows and vice versa is at least spelled as it should!
Yesterday, I could not run the column macro getting a error 91 message, but this morning, it all works OK. I have no idea why that should be. Googling gave me the feel that it is usually when there is a "find" statement?
Thank you for taking time to look at revising the macro. The one I tried to write up based on the column macro is called" MakeNames_Row1".
Have a great day,
Danièle


Example--row-columns-.xls
0
 

Author Comment

by:daniques
ID: 34990271
I have a deadline which means that I have worked too late to think straight!
I am joining the sample sheet with a revised macro for unique entries in the first row.

The line that stops the macro is the same that was used for the other macro, but I believe that this part might be the incorrect part that stops all?

    If Application.CountIf(Range(lngCol & "B1:1"), Range(lngCol & "1")) = 1 Then

I am not sure at all of the syntax here.

I am getting quite desperate because of the deadline and I have several dozens of sheets that need named ranges and so I am now doing it manually which is always open to mistake.
I would really really appreciate any help in giving me some light as to what is the problem?
I would also appreciate if someone could shed light to my error 91 I was getting yesterday and that disappeared once I stopped excel AND stopped my computer,
Thanks!
Danièle


Example--row-columns-2.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34991132
Danièle,

Please forget what's been done before. I need your answer on this before I can help:

Can you confirm what you wanting to achieve. Are you wanting, for example, the range C2:C6 to be named 'a' and so on?

Patrick
0
 

Author Comment

by:daniques
ID: 34994438
Hi Patrick,
I sent an answer this morning but it did not seem to go through; server issues.
Well, if you look at the table in the example file that I first sent, the columns have all different headings, so yes, in this case, each range would be called "Range- + the letter that heads the column, such as a for column C2-C6.
I sent a second example file ( example(row-columns) where I changed the headings so that three columns had the same letter, and the next 2 another letter etc. I am sending a third example file, stripped of what 's been done before. The idea is to name the ranges in such a way that for every unique entry, if there are several columns with the same entry, to group them together, then name them based on the heading like Range-A, if the heading is A.
The columns can first be grouped to gather by using sorting.
The number of columns can and will be variable for the same heading.
I really hope example 3 will help,
Cheers,
Danièle

Example-3.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34996042
Danièle,

I am now much clearer what you are wanting. Whether I can produce a macro to solve the problem I do not at this moment know! I'll be back, one way or another later today.

Patrick
0
 

Author Comment

by:daniques
ID: 34996250
Thanks!
I am not sure what you meant later in the day in Australia here or other place in the world? I am sadly loging out for the day, so will not be able to respond before the next 10 hours.
Thanks for looking into that!

 I thought adapting the macro that is naming ranges based on entries in the first column would be easy, and my attempt is with the example file  with the module called MakeNamesFromGroupeRowEntries.
The main problem was to convert the "co"l that were declared as integer  to alphabetical entries to describe the ranges....hence the added function  ConvertToLetter

Anyway, I have completed the project by hand which means that I ma not under pressure and so will enjoy the answer and be able to really go through it with a fine comb to learn. I'll be more ready for the next time which is bound to happen!

Have a nice day,

Danièle
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34997125
Danièle,

The code below is in the attached file.

Hope it helps

Patrick


Sub specialmacro()
Dim rng As Range
Dim rng2 As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long
Dim startcol As Long
Dim endcol As Long
Dim rngname As String

With Sheets("Sheet1")
    Set rng = Range(.Cells(2, "D"), .Cells(2, "M"))
End With

For Each celle In rng
    If celle <> celle.Offset(0, -1) Then
        startcol = celle.Column
        endcol = 0
        rngname = "Range_" & celle
    End If
    If celle <> celle.Offset(0, 1) Then
        endcol = celle.Column
    End If
    If startcol > 0 And endcol > 0 Then
        With Sheets("Sheet1")
            Set rng2 = .Range(.Cells(3, startcol), .Cells(7, endcol))
            ActiveWorkbook.Names.Add Name:=rngname, RefersTo:="""Sheet1!""" & rng2.Address
        End With
    End If
Next celle

End Sub

Open in new window

named-ranges-03.xls
0
 

Author Comment

by:daniques
ID: 35003066
Hi Patrick,
It works perfectly... but is not dynamic column wise

The code has hard coded the number of columns( C to M). It is dynamic down the rows though!

I was wondering whether the code could request the user to enter the total range in the understanding that the first row contains the heading and the first  column the ID?
I am not sure whether that would help? I was planning to include in the code a sub module to first sort the columns so that they get grouped prior to the naming process.
Thanks!
Danièle
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35005826
Danièle,

The ranges are now dynamic. For the macro to work it needs a cell containing "ID#" and a cell at the right hand end of the range name headers containing any word so long as it is not a range name - so for example the word 'Headings' is OK.

It's all in the attached file - code below.

Patrick
Sub specialmacro()
Dim rng As Range
Dim rng2 As Range
Dim celle As Range
Dim startcol As Long
Dim endcol As Long
Dim rngname As String
Dim IDcellcolm As Long
Dim IDcellrow As Long
Dim IDcellrng As Range
Dim namesrng As Range
Dim lastrow As Long

With Sheets("Sheet1")
    .Activate
    Set rng = .UsedRange
End With

For Each celle In rng
    If celle = "ID#" Then
        IDcellcolm = celle.Column
        IDcellrow = celle.Row
        lastrow = celle.End(xlDown).Row
    End If
Next celle

Set namesrng = Range(Cells(IDcellrow, IDcellcolm + 1), Cells(IDcellrow, IDcellcolm + 1).End(xlToRight).Offset(0, -1))

For Each celle In namesrng
    If celle <> celle.Offset(0, -1) Then
        startcol = celle.Column
        endcol = 0
        rngname = "Range_" & celle
    End If
    If celle <> celle.Offset(0, 1) Then
        endcol = celle.Column
    End If
    If startcol > 0 And endcol > 0 Then
        With Sheets("Sheet1")
            Set rng2 = .Range(.Cells(IDcellrow + 1, startcol), .Cells(lastrow, endcol))
            ActiveWorkbook.Names.Add Name:=rngname, RefersTo:="Sheet1!" & rng2.Address
        End With
    End If
Next celle

End Sub

Open in new window

named-ranges-04.xls
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:daniques
ID: 35006182
Hi Patrick!

The macro does generate the ranges, and what is great is that the table can be anywhere on a spreadsheet, if there is an empty row after the table, it will define where is the last row is and the same for the columns.

There is however still one problem.

 It does not recognise the names in any formulas I write, nor will any of the names appear in the name cell above the spreadsheet. When I go to insert, name, define, the named ranges are there with the correct description but they have quotation marks  

="Sheet1!A2:C6" rather than =Sheet1!A2:C6.

 When I manually remove the quotation marks, , the named range appear in the name cells in the spreadsheet and any formula with a named range gets calculated.
I have run through the macro, but having little syntax knowledge, I could not figure how those quotations could be removed or where they were generated.
 
I am quite excited as I can work out the logic of it all which is great fun.
Thanks for the help, and I hope the last little hurdle will be a mere short rewriting for you!
Have a very nice day,

Cheers,
Danièle
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35006523
Danièle,

>Thanks for the help, and I hope the last little hurdle will be a mere short rewriting for you!

That's not proved to be the case. The MsgBox shows what's being used for each range and yet it doesn't come out that way.

Patrick
Sub specialmacro()
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim celle As Range
Dim startcol As Long
Dim endcol As Long
Dim rngname As String
Dim IDcellcolm As Long
Dim IDcellrow As Long
Dim IDcellrng As Range
Dim namesrng As Range
Dim lastrow As Long
Dim str1 As String
Dim refsto As String

str1 = "Range_"
Set ws = Sheets("Sheet1")

With Sheets("Sheet1")
    .Activate
    Set rng = .UsedRange
End With

For Each celle In rng
    If celle = "ID#" Then
        IDcellcolm = celle.Column
        IDcellrow = celle.Row
        lastrow = celle.End(xlDown).Row
    End If
Next celle

Set namesrng = Range(Cells(IDcellrow, IDcellcolm + 1), Cells(IDcellrow, IDcellcolm + 1).End(xlToRight).Offset(0, -1))

For Each celle In namesrng
    If celle <> celle.Offset(0, -1) Then
        startcol = celle.Column
        endcol = 0
        rngname = str1 & celle
    End If
    If celle <> celle.Offset(0, 1) Then
        endcol = celle.Column
    End If
    If startcol > 0 And endcol > 0 Then
        With Sheets("Sheet1")
            Set rng2 = .Range(.Cells(IDcellrow + 1, startcol), .Cells(lastrow, endcol))
            refsto = "Sheet1!" & rng2.Address
            MsgBox "Refers to " & refsto
            ActiveWorkbook.Names.Add Name:=rngname, RefersTo:=refsto
        End With
    End If
Next celle

End Sub

Open in new window

named-ranges-06.xls
0
 

Author Comment

by:daniques
ID: 35016286
Dear Patrick,
It seems the "sheet1!" part is sort of messing the whole thing up and if the sheet could be included in the Set rng2 = .Range(.Cells(IDcellrow + 1, startcol), .Cells(lastrow, endcol))
part then the step
refsto = "Sheet1!" & rng2.Address could be by passed, eliminating any indirect referring which seems to be the problem? I am definitely not at all savvy in VBA so am just trying to eliminate indirect steps which I find right or wrongly the biggest culprits of "wrong" actions?
I really am sorry because all the logic is there, perfect.
So all holds on how to refer to the logic to build up the range name without having quotation marks.
Thanks a stack for trying and this last hurdle is probably the one grain of sand that blocks the cogs which must be very frustrating.
I hope to read that you have mastered the problem.
Thanks a stack,
Danièle
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35016784
Danièle,

The issue is that this line of code messes it up:

ActiveWorkbook.Names.Add Name:=rngname, RefersTo:=refsto

I have no idea why nor indeed how it does it, but it does.

The string refsto is correct but when it is used in the above line of code it goes wrong!

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35025888
Danièle,

In the attached file, when you run the macro, you will see a MsgBox that shows you what string is used for every RefersTo . You will see that ALL of them look OK but they don't turn out that way.

Patrick
named-ranges-06.xls
0
 

Author Comment

by:daniques
ID: 35026298
Dear Patrick,

They do look OK....
When hovering over the VBA code, on the line
 refsto = "Sheet1!" & rng2.Address
the comment box appears saying that refsto="Sheet1! $D$3:$F7" within quotation mark.
The message does not show the quotation mark but maybe because it reads it as a string.. because of the quotation mark?
when hovering over the  rng2.Address, the comment box also gives a string in quotation marks as rng2.Adress = "$D$3:$F$7" .
It seems that refsto is the concatenation of 2 strings rather than an actual address?
II am hopeless at macro, so I would not be able to start trying to figure out how to change that, but I have the gut feeling that is what the problem.
Baffling baffling!
Thanks!
Danièle

0
 
LVL 45

Expert Comment

by:patrickab
ID: 35026498
Danièle,

>I am hopeless at macro, so I would not be able to start trying to figure out how to change that, but I have the gut feeling that is what the problem.

I'm afraid you're not correct. It has nothing to do with the fact that refsto is the concatenation of 2 strings.

As you can see when you run the macro the refsto string does NOT have quotation marks - and yet the VBA adds the quotation marks automatically. when used in this line of code:

ActiveWorkbook.Names.Add Name:=rngname, RefersTo:=refsto

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35035030
Danièle,

I have tried changing the VBA every whichway but without success. I have even tried R1C1 notation for the refsto but that has not solved the problem.

Patrick
0
 

Author Comment

by:daniques
ID: 35035143
Hi Patrick,

If you change the way you address the naming of the range, it works which is what I was trying to explain and do.
 It seems that addressing the way you did, the resulting concatenated string is picked up as a string rather than an address.
I tried a few different way of "addressing" and finally, I simply changed the following line

 ActiveWorkbook.Names.Add Name:=rngname, RefersTo:=refsto

to

 ActiveWorkbook.Names.Add Name:=rngname, RefersTo:=Worksheets("Sheet1").Range(.Cells(IDcellrow + 1, startcol), .Cells(lastrow, endcol))

by passing the string altogether, and goind directly to what the string referred to.

The macro now works just as you had planned!

Thanks!

Danièle

Sub specialmacro()
Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim celle As Range
Dim startcol As Long
Dim endcol As Long
Dim rngname As String
Dim IDcellcolm As Long
Dim IDcellrow As Long
Dim IDcellrng As Range
Dim namesrng As Range
Dim lastrow As Long
Dim str1 As String
Dim refsto As String

str1 = "Range_"
Set ws = Sheets("Sheet1")

With Sheets("Sheet1")
    .Activate
    Set rng = .UsedRange
End With

For Each celle In rng
    If celle = "ID#" Then
        IDcellcolm = celle.Column
        IDcellrow = celle.Row
        lastrow = celle.End(xlDown).Row
    End If
Next celle

Set namesrng = Range(Cells(IDcellrow, IDcellcolm + 1), Cells(IDcellrow, IDcellcolm + 1).End(xlToRight).Offset(0, -1))

For Each celle In namesrng
    If celle <> celle.Offset(0, -1) Then
        startcol = celle.Column
        endcol = 0
        rngname = str1 & celle
    End If
    If celle <> celle.Offset(0, 1) Then
        endcol = celle.Column
    End If
    If startcol > 0 And endcol > 0 Then
        With Sheets("Sheet1")
            Set rng2 = .Range(.Cells(IDcellrow + 1, startcol), .Cells(lastrow, endcol))
            refsto = "Sheet1!" & rng2.Address
            MsgBox "Refers to " & refsto
            ActiveWorkbook.Names.Add Name:=rngname, RefersTo:=Worksheets("Sheet1").Range(.Cells(IDcellrow + 1, startcol), .Cells(lastrow, endcol))

        End With
    End If
Next celle

End Sub

Open in new window

named-ranges-06-NU-.xls
0
 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
ID: 35035173
Danièle,

I have changed it to:

With Sheets("Sheet1")
            Set rng2 = .Range(.Cells(IDcellrow + 1, startcol), .Cells(lastrow, endcol))
            ActiveWorkbook.Names.Add Name:=rngname, RefersToLocal:=.Range(.Cells(IDcellrow + 1, startcol), .Cells(lastrow, endcol))
        End With

and it works OK. It's in the attached file.

Patrick
named-ranges-09.xls
0
 

Author Comment

by:daniques
ID: 35035366
Hi Patrick,

Your changes makes it even more versatile which is great.
 I might replace in the message part  as it is a quick and effective ways of checking all is correct, but that is easy enough to do.

Thanks!

Danièle
0
 

Author Closing Comment

by:daniques
ID: 35035374
very versatile VBA code that can be used on tables in any worksheet, in any position which is great.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35035392
Danièle - Thanks for the grade - Patrick
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now