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

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

Post by Sm9lIE1hY » Sat, 15 Nov 2008 23:42:02


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
 
 
 

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

Post by Don Guille » Sun, 16 Nov 2008 00:08:59

You are probably incrementing your "new coverage code count" within the loop

sub makenames()
Sheets("Coverage Codes").Select
NewCoverageCodeCount = range("a1").CurrentRegion.Rows.Count

Range("A2:A" & NewCoverageCodeCount).name="CoverageCodes"
Range("B2:B" & NewCoverageCodeCount).Name="CoverageMonths"
'etc for c & d

end sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
XXXX@XXXXX.COM

 
 
 

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

Post by Jim Con » Sun, 16 Nov 2008 00:22:50

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





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
 
 
 

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

Post by Jim Con » Sun, 16 Nov 2008 00:41:18

Correction...
If the NewCoverageCodes range is "C2:C7" then _
Range("NewCoverageCodes").Range("C2") is equal to the cell
in the second row, third column of NewCoverageCodes or cell E3.
--
Jim Cone
Portland, Oregon USA
 
 
 

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

Post by Sm9lIE1hY » Sun, 16 Nov 2008 02:51:01

hank you - This helped get me through this first hurdle... now on to the
next Looping step... I appreciate the assistance very much...
Joe
--

Joe Mac


"Don Guillett" wrote:

 
 
 

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

Post by Don Guille » Sun, 16 Nov 2008 04:40:30

hat I sent was NOT a loop.....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
XXXX@XXXXX.COM
"Joe Mac" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...

 
 
 

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

Post by Sm9lIE1hY » Sun, 16 Nov 2008 05:54:01

i Don...

Yes I know... The full extent of the program that I'm attempting to build
includes a Loop that will cycle through a range of data, "CoverageCodes" from
the earlier stream, and subsequently update a template range of data... the
updated template data will then be appended to create a Master Table...

I'm having troubles at this point the Loop appears to be defined as
infinite, it's just not hitting the target...

I've attached the Loop segment if you the time to review what I've defined
incorrectly, I'd be much appreciative..

LoopCount = 0

Do Until LoopCount > NewCoverageCodeCount

For i = 1 To i > TemplateRowCount
LoopCount = (LoopCount + 1)
Range("TemplateCoverageCode").Cells(i) =
Range("CoverageCode").Cells(i)
Range("TemplateCoverageMonths").Cells(i) =
Range("CoverageMonth").Cells(i)
Range("TemplateCoverageMiles").Cells(i) =
Range("CoverageMiles").Cells(i)
Range("TemplateCoverageEffectiveDate").Cells(i) =
Range("CoverageEffectiveDate").Cells(i)

Range("A3").Select
Selection.CurrentRegion.Select

Application.CopyObjectsWithCells = True
Selection.Copy

Sheets("Update Master").Select
MasterRowCount = ((Selection.CurrentRegion.Rows.Count) + 1)
Range("A" & MasterRowCount).Select
Application.ActiveCell.PasteSpecial Paste:=xlPasteAll
Sheets("Copy Template").Select
Range("A3").Select
Next
Loop




--

Joe Mac


"Don Guillett" wrote:

 
 
 

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

Post by Don Guille » Sun, 16 Nov 2008 07:28:08

Hard to tell from segments. If desired, send your wb to my address below
along with instructions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
XXXX@XXXXX.COM
"Joe Mac" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...