ExcelBanter - What am I doing wrong with Ranges named within Macro...

All things are relative (in this situation anyway)...

If the NewCoverageCodes range is "C2:C7" then _
Range("NewCoverageCodes").Range("C2") is equal to the cell
in the second row, third column of CoverageMiles or cell E3.
And _
Range("NewCoverageCodes").Range("A1") is cell C2.

Also, subtract 1 (one) from the count when you specify the new range.
--
Jim Cone
Portland, Oregon USA

"Joe Mac" wrote in message ...
All...
I thank all in advance -

Attached is the code that I've built to identify a range and Define a name
for use in a workbook... As I step through the code it properely outlines
the range as is defined, however when I view the range in Excel it is not as
I expected...
The first named range always works - NewCoverageCodes, however after that
each named range is incorrect
CoverageCodes in Excel is defined as ='Coverage Codes'!$A$3:$A$8 when I
expected it to be 'Coverage Codes'!$A$2:$A$7 and
CoverageMonths in Excel is defined as ='Coverage Codes'!$C$3:$C$8 when I
expected it to be 'Coverage Codes'!$B$2:$B$7 and
CoverageMiles in Excel is defined as ='Coverage Codes'!$E$3:$E$8 when I
expected it to be 'Coverage Codes'!$C$2:$C$7 and

Sub AddCoverageCodes()
Dim NewCoverageCodes As Range
Dim CoverageCodes As Range
Dim CoverageMonths As Range
Dim CoverageMiles As Range
Dim CoverageEffectiveDate As Range
Dim i As Integer
Dim LoopCount As Long
Dim NewCoverageCodeCount As Long

Sheets("Coverage Codes").Select
Range("A1").Select
NewCoverageCodeCount = (Selection.CurrentRegion.Rows.Count)

Selection.CurrentRegion.Select
Set NewCoverageCodes = (Selection.CurrentRegion)
ActiveWorkbook.Names.Add Name:="NewCoverageCodes",
RefersTo:=NewCoverageCodes

Range("A2:A" & NewCoverageCodeCount).Select
Set CoverageCodes = (Selection.Range("A2:A" & NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageCodes", RefersTo:=CoverageCodes

Range("B2:B" & NewCoverageCodeCount).Select
Set CoverageMonths = (Selection.Range("B2:B" & NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageMonths", RefersTo:=CoverageMonths

Range("C2:C" & NewCoverageCodeCount).Select
Set CoverageMiles = (Selection.Range("C2:C" & NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageMiles", RefersTo:=CoverageMiles

Range("D2:D" & NewCoverageCodeCount).Select
Set CoverageEffectiveDate = (Selection.Range("C2:C" &
NewCoverageCodeCount))
ActiveWorkbook.Names.Add Name:="CoverageEffectiveDate",
RefersTo:=CoverageEffectiveDate

--

Joe Mac