Define Named ranges via VBA Macro

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
I have the following spreadsheet (that is brought in from a query):
TEMPLATEProductFamily-ReportedCode-Test.xls
ABCD
1PRODUCT_FAMILYPRODUCT_SEGMENT
2HerculinkBalloonExpandStent
3HerculinkBiliaryBalloonExpandStent
4HerculinkEliteBalloonExpandStent
5HerculinkEliteBiliaryBalloonExpandStent
6HerculinkPlusBalloonExpandStent
7HerculinkPlusBiliaryBalloonExpandStent
8MegalinkBalloonExpandStent
9MegalinkBiliaryBalloonExpandStent
10NeurolinkBalloonExpandStent
11OTWMegalinkBalloonExpandStent
12OTWMegalinkBiliaryBalloonExpandStent
13Omnilink18BalloonExpandStent
14Omnilink18BiliaryBalloonExpandStent
15Omnilink35BalloonExpandStent
16Omnilink35BiliaryBalloonExpandStent
17UnknownOmnilinkBalloonExpandStent
18OTWAcculinkCarotid
19RXAcculinkCarotid
20RXAccunetCarotid
21RXAccunet3-IN-1Carotid
22UnknownAccunetCarotid
23Agiltrac18Dilatation
24Agiltrac35Dilatation
25OTWViatracDilatation
26RXViatracDilatation
27RXViatracPlusDilatation
28UnknownAgiltracDilatation
29NeuronetESClinical
30OTWAcculinkClinicalESClinical
31OTWAccunetClinicalESClinical
32RXAcculinkClinicalESClinical
33RXAccunetClinicalESClinical
34HTSupraCore35GuideWire
35SpartacoreGuideWire
36Steelcore18GuideWire
37Steelcore18LTGuideWire
38VeripathGuidingCatheters
39Absolute35SelfExpandStent
40Dynalink18SelfExpandStent
41Dynalink18BiliarySelfExpandStent
42Dynalink35SelfExpandStent
43Dynalink35BiliarySelfExpandStent
44DynalinkGrande35SelfExpandStent
45DynalinkGrande35BiliarySelfExpandStent
Product_Family


1. I need to do a couple things: I need to insert "ALL" in between each Product Family (ColumnA) at each change in Product Segment (Column B)
and then fill in the blank in the Product Segment with the appropriate Product Segment
Something like this:<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - TEMPLATEProductFamily-ReportedCode-Test.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 11.0 : OS = Windows XP</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb605117><INPUT onclick='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);' type=button value="Copy Formula" name=btCb942116></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb202339><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362><OPTION value="" selected>B31</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value=Dilatation name=txbFb150492></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">PRODUCT_FAMILY</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">PRODUCT_SEGMENT</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">ALL</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Balloon Expand Stent</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Herculink</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Balloon Expand Stent</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Herculink Biliary</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Balloon Expand Stent</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Herculink Elite</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Balloon Expand Stent</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Herculink Elite Biliary</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Balloon Expand Stent</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>7</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 1
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Apparently I went over the limit on one posting: here's the rest of what I posted. This is the example of how I need ALL inserted above the Product Family (Column A) at each change in Product Segment (Column B)
TEMPLATEProductFamily-ReportedCode-Test.xls
ABCD
1PRODUCT_FAMILYPRODUCT_SEGMENT
2ALLBalloonExpandStent
3HerculinkBalloonExpandStent
4HerculinkBiliaryBalloonExpandStent
5HerculinkEliteBalloonExpandStent
6HerculinkEliteBiliaryBalloonExpandStent
7HerculinkPlusBalloonExpandStent
8HerculinkPlusBiliaryBalloonExpandStent
9MegalinkBalloonExpandStent
10MegalinkBiliaryBalloonExpandStent
11NeurolinkBalloonExpandStent
12OTWMegalinkBalloonExpandStent
13OTWMegalinkBiliaryBalloonExpandStent
14Omnilink18BalloonExpandStent
15Omnilink18BiliaryBalloonExpandStent
16Omnilink35BalloonExpandStent
17Omnilink35BiliaryBalloonExpandStent
18UnknownOmnilinkBalloonExpandStent
19ALLCarotid
20OTWAcculinkCarotid
21RXAcculinkCarotid
22RXAccunetCarotid
23RXAccunet3-IN-1Carotid
24UnknownAccunetCarotid
25ALLDilatation
26Agiltrac18Dilatation
27Agiltrac35Dilatation
28OTWViatracDilatation
29RXViatracDilatation
30RXViatracPlusDilatation
31UnknownAgiltracDilatation
Product_Family


Lastly, I need to Define Named Ranges for each group of Product Segments. I need this all done in VB Macro for an ongoing VB program that automates some Charts/Graphs, etc
 
Upvote 0
Try this.
Code:
Sub test()
Dim rng As Range
    Set rng = Range("B1")
    
    While rng.Value <> ""
        If rng.Value <> rng.Offset(1) And rng.Offset(1) <> "" Then
            rng.Offset(1).EntireRow.Insert
            rng.Offset(1, -1) = "ALL"
            rng.Offset(1) = rng.Offset(2)
            rng.Offset(1, -1).Resize(, 2).Font.Bold = True
            Set rng = rng.Offset(1)
        End If
        Set rng = rng.Offset(1)
    Wend
End Sub
By the way your title is a little confusing since you don't actually mention named ranges in the post.
 
Upvote 0
Thanks, the code you gave for inserting ALL portion of my request worked great. Now I need some assistance on creating named ranges basesd off the Product Segment

you stated :"By the way your title is a little confusing since you don't actually mention named ranges in the post."

Actually I think it was opposite. I mentioned in my original post subject line: Define Named ranges via VBA Macro
I didn't mention anything about inserting rows in between change in value.

Does anyone have any suggestions/help for setting defined name ranges .
 
Upvote 0
Sorry, didn't see the last part of your original post.:oops:

What named ranges do you actually need defined?
 
Upvote 0
Basically I need Column A (PRODUCT_FAMILY) defined as it's PRODUCT_SEGMENT (Column B) name
So in example above I would need A2:A13 defined as "Balloon Expand Stent".

here's the code I had for a similar set of data...but I can't get it to work:

Code:
'Set Defined Name Ranges for all Product Segments
    Dim rng As Range
Dim s As Long
    s = 2
With Sheets("Product_Family")
        Set rng = .Range("B2")
        While rng.Value <> ""
            If rng.Value <> rng.Offset(1) Then
                .Range("B" & s & ":B" & rng.Row).Offset(, -1).Name = rng.Value
                s = rng.Row + 1
    End If
        Set rng = rng.Offset(1)
    Wend
End With
 
Upvote 0
I ran the vb code line by line using the F8 key and it makes it down to this line of code, and I can't figure out why it's not working:
Code:
'Set Defined Name Ranges for all Product Segments 
    Dim rng As Range 
Dim s As Long 
    s = 2 
With Sheets("Product_Family") 
        Set rng = .Range("B2") 
        While rng.Value <> "" 
            If rng.Value <> rng.Offset(1) Then 
                .Range("B" & s & ":B" & rng.Row).Offset(, -1).Name = rng.Value 
                s = rng.Row + 1 
    End If 
        Set rng = rng.Offset(1) 
    Wend 
End With

this line:
Code:
.Range("B" & s & ":B" & rng.Row).Offset(, -1).Name = rng.Value
is where it bails.

Any help/suggestions?
 
Upvote 0
Please Help--Need Defined Name Range for VB

Please someone help me figure out why my code is not working.
I have two columns of data (example above somewhere in this thread)
where I need to define named ranges using column A (Product_Family) as the defined range with each change in defined name range based off of Column B (Product_Segment)

Here's my code that I thought would work (because it works for another almost exact worksheet).

Code:
'Set Defined Name Ranges for all Product Segments 
    Dim rng As Range 
Dim s As Long 
    s = 2 
With Sheets("Product_Family") 
        Set rng = .Range("B2") 
        While rng.Value <> "" 
            If rng.Value <> rng.Offset(1) Then 
                .Range("B" & s & ":B" & rng.Row).Offset(, -1).Name = rng.Value 
                s = rng.Row + 1 
    End If 
        Set rng = rng.Offset(1) 
    Wend 
End With

Somehow it bails on this line (I used the Step Into with F8 to find where the problem was).

Code:
.Range("B" & s & ":B" & rng.Row).Offset(, -1).Name = rng.Value
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top