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

J

Joe Mac

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
 
D

Don Guillett

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
 
J

Jim Cone

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
 
J

Jim Cone

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.
 
J

Joe Mac

Thank 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 said:
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
Click to expand...
 
D

Don Guillett

What I sent was NOT a loop.....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Joe Mac said:
Thank you - This helped get me through this first hurdle... now on to the
next Looping step... I appreciate the assistance very much...
Joe
Click to expand...
 
J

Joe Mac

Hi 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
 
D

Don Guillett

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

InputBox to name a range 2
Need values not formulas - CopyMultipleSelection 2
VLookup in VBA Help Needed 9
Delete Named rnage and Add Named Range 5
Maximum Named Ranges in excel 0
Advice on multiple macros, same code just a different column 9
Copy Worksheet with Named Ranges 2
Problem with progamatically defined named ranges 3

Top