Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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" wrote: 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 "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 |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
What I sent was NOT a loop.....
-- Don Guillett Microsoft MVP Excel SalesAid Software "Joe Mac" wrote in message ... 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" wrote: 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 "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 |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 -- Joe Mac "Don Guillett" wrote: What I sent was NOT a loop..... -- Don Guillett Microsoft MVP Excel SalesAid Software "Joe Mac" wrote in message ... 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" wrote: 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 "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 |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() Hard to tell from segments. If desired, send your wb to my address below along with instructions. -- Don Guillett Microsoft MVP Excel SalesAid Software "Joe Mac" wrote in message ... 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 -- Joe Mac "Don Guillett" wrote: What I sent was NOT a loop..... -- Don Guillett Microsoft MVP Excel SalesAid Software "Joe Mac" wrote in message ... 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" wrote: 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 "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 |
#7
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#8
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
macro to make named ranges | Excel Programming | |||
Referring to named ranges in a macro | Excel Programming | |||
Macro eliminating named ranges | Excel Programming | |||
Named Ranges - Macro Problems | Excel Programming |