| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with dynamic named range
Hopefully there is a solution to my quandary this time. I am trying to
evaluate cells in a list to confirm that they are in a validation list. Yes, I know that validation should have been used during entry, but it was circumvented and I want to cycle through the cells and highlight any that are not in the validation list. My problem is that the following example code works when addressing a range by cell reference (i.e. "c2:c9") and with a defined name range that is limited (i.e. Shifts = "f3:f6"). But when I make the defined name range dynamic, it stops working (i.e. Shifts = offset(f3,0,0,counta(f3:f100),1) ). The cell loop goes across the entire sheet, as it was from a more complicated example. Could someone please explain what I am missing here? Sub checkvalidation() Dim LastRow As Long, LastCol As Long, rowstep As Long, colstep As Long, validtype As Long Dim validrng As Range Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo errhandler LastRow = Cells.SpecialCells(xlLastCell).Column LastCol = Cells.SpecialCells(xlLastCell).Column On Error Resume Next For colstep = 1 To Last Col For rowstep = 1 To LastRow validtype = Cells(rowstep, colstep).Validation.Type If validtype = 3 Then Set validrng = Range("shifts") If IsError(Application.WorksheetFunction.Match(Cells( rowstep, colstep), validrng, 0)) Then With Cells(rowstep, colstep) .Interior.ColorIndex = 45 End With End If End If Next rowstep Next colstep errhandler: Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with dynamic named range
First things first. I think this line is incorrect...
LastRow = Cells.SpecialCells(xlLastCell).Column you probably mean LastRow = Cells.SpecialCells(xlLastCell).Row -- HTH... Jim Thomlinson " wrote: Hopefully there is a solution to my quandary this time. I am trying to evaluate cells in a list to confirm that they are in a validation list. Yes, I know that validation should have been used during entry, but it was circumvented and I want to cycle through the cells and highlight any that are not in the validation list. My problem is that the following example code works when addressing a range by cell reference (i.e. "c2:c9") and with a defined name range that is limited (i.e. Shifts = "f3:f6"). But when I make the defined name range dynamic, it stops working (i.e. Shifts = offset(f3,0,0,counta(f3:f100),1) ). The cell loop goes across the entire sheet, as it was from a more complicated example. Could someone please explain what I am missing here? Sub checkvalidation() Dim LastRow As Long, LastCol As Long, rowstep As Long, colstep As Long, validtype As Long Dim validrng As Range Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo errhandler LastRow = Cells.SpecialCells(xlLastCell).Column LastCol = Cells.SpecialCells(xlLastCell).Column On Error Resume Next For colstep = 1 To Last Col For rowstep = 1 To LastRow validtype = Cells(rowstep, colstep).Validation.Type If validtype = 3 Then Set validrng = Range("shifts") If IsError(Application.WorksheetFunction.Match(Cells( rowstep, colstep), validrng, 0)) Then With Cells(rowstep, colstep) .Interior.ColorIndex = 45 End With End If End If Next rowstep Next colstep errhandler: Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with dynamic named range
Yeah, sorry about that, it was a quick cut and paste to replace a temp
number while I was trying to trap the problem. Still doesn't work. marc Jim Thomlinson wrote: First things first. I think this line is incorrect... LastRow = Cells.SpecialCells(xlLastCell).Column you probably mean LastRow = Cells.SpecialCells(xlLastCell).Row -- HTH... Jim Thomlinson " wrote: Hopefully there is a solution to my quandary this time. I am trying to evaluate cells in a list to confirm that they are in a validation list. Yes, I know that validation should have been used during entry, but it was circumvented and I want to cycle through the cells and highlight any that are not in the validation list. My problem is that the following example code works when addressing a range by cell reference (i.e. "c2:c9") and with a defined name range that is limited (i.e. Shifts = "f3:f6"). But when I make the defined name range dynamic, it stops working (i.e. Shifts = offset(f3,0,0,counta(f3:f100),1) ). The cell loop goes across the entire sheet, as it was from a more complicated example. Could someone please explain what I am missing here? Sub checkvalidation() Dim LastRow As Long, LastCol As Long, rowstep As Long, colstep As Long, validtype As Long Dim validrng As Range Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo errhandler LastRow = Cells.SpecialCells(xlLastCell).Column LastCol = Cells.SpecialCells(xlLastCell).Column On Error Resume Next For colstep = 1 To Last Col For rowstep = 1 To LastRow validtype = Cells(rowstep, colstep).Validation.Type If validtype = 3 Then Set validrng = Range("shifts") If IsError(Application.WorksheetFunction.Match(Cells( rowstep, colstep), validrng, 0)) Then With Cells(rowstep, colstep) .Interior.ColorIndex = 45 End With End If End If Next rowstep Next colstep errhandler: Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with dynamic named range
Be careful in the use of these names. If "Shifts" refers to cells
A1:A5 and then you use Set validrng = worksheets("Sheet1").range("Shifts") If "Shifts" then refers to cells A1:A10 later on, validrng will still refer to cells A1:A5. You may already know that, but I thought I would mention it in case you didn't. Mark wrote: It appears that the problem is that, in order to address a dynamic range, validrng needs to be set with a worksheet specified. So that, Set validrng = worksheets("Sheet1").range("Shifts") WILL work, but: Set validrng = Range("Shifts") will NOT. Can someone explain this to me? wrote: Yeah, sorry about that, it was a quick cut and paste to replace a temp number while I was trying to trap the problem. Still doesn't work. marc Jim Thomlinson wrote: First things first. I think this line is incorrect... LastRow = Cells.SpecialCells(xlLastCell).Column you probably mean LastRow = Cells.SpecialCells(xlLastCell).Row -- HTH... Jim Thomlinson " wrote: Hopefully there is a solution to my quandary this time. I am trying to evaluate cells in a list to confirm that they are in a validation list. Yes, I know that validation should have been used during entry, but it was circumvented and I want to cycle through the cells and highlight any that are not in the validation list. My problem is that the following example code works when addressing a range by cell reference (i.e. "c2:c9") and with a defined name range that is limited (i.e. Shifts = "f3:f6"). But when I make the defined name range dynamic, it stops working (i.e. Shifts = offset(f3,0,0,counta(f3:f100),1) ). The cell loop goes across the entire sheet, as it was from a more complicated example. Could someone please explain what I am missing here? Sub checkvalidation() Dim LastRow As Long, LastCol As Long, rowstep As Long, colstep As Long, validtype As Long Dim validrng As Range Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo errhandler LastRow = Cells.SpecialCells(xlLastCell).Column LastCol = Cells.SpecialCells(xlLastCell).Column On Error Resume Next For colstep = 1 To Last Col For rowstep = 1 To LastRow validtype = Cells(rowstep, colstep).Validation.Type If validtype = 3 Then Set validrng = Range("shifts") If IsError(Application.WorksheetFunction.Match(Cells( rowstep, colstep), validrng, 0)) Then With Cells(rowstep, colstep) .Interior.ColorIndex = 45 End With End If End If Next rowstep Next colstep errhandler: Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Trouble with dynamic named range
Thanks, Mark. It's definitely a fine point I might not have realized,
but validrng would be changed through each loop and when the macro was called, so that should be ok. Mark Driscol wrote: Be careful in the use of these names. If "Shifts" refers to cells A1:A5 and then you use Set validrng = worksheets("Sheet1").range("Shifts") If "Shifts" then refers to cells A1:A10 later on, validrng will still refer to cells A1:A5. You may already know that, but I thought I would mention it in case you didn't. Mark wrote: It appears that the problem is that, in order to address a dynamic range, validrng needs to be set with a worksheet specified. So that, Set validrng = worksheets("Sheet1").range("Shifts") WILL work, but: Set validrng = Range("Shifts") will NOT. Can someone explain this to me? wrote: Yeah, sorry about that, it was a quick cut and paste to replace a temp number while I was trying to trap the problem. Still doesn't work. marc Jim Thomlinson wrote: First things first. I think this line is incorrect... LastRow = Cells.SpecialCells(xlLastCell).Column you probably mean LastRow = Cells.SpecialCells(xlLastCell).Row -- HTH... Jim Thomlinson " wrote: Hopefully there is a solution to my quandary this time. I am trying to evaluate cells in a list to confirm that they are in a validation list. Yes, I know that validation should have been used during entry, but it was circumvented and I want to cycle through the cells and highlight any that are not in the validation list. My problem is that the following example code works when addressing a range by cell reference (i.e. "c2:c9") and with a defined name range that is limited (i.e. Shifts = "f3:f6"). But when I make the defined name range dynamic, it stops working (i.e. Shifts = offset(f3,0,0,counta(f3:f100),1) ). The cell loop goes across the entire sheet, as it was from a more complicated example. Could someone please explain what I am missing here? Sub checkvalidation() Dim LastRow As Long, LastCol As Long, rowstep As Long, colstep As Long, validtype As Long Dim validrng As Range Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo errhandler LastRow = Cells.SpecialCells(xlLastCell).Column LastCol = Cells.SpecialCells(xlLastCell).Column On Error Resume Next For colstep = 1 To Last Col For rowstep = 1 To LastRow validtype = Cells(rowstep, colstep).Validation.Type If validtype = 3 Then Set validrng = Range("shifts") If IsError(Application.WorksheetFunction.Match(Cells( rowstep, colstep), validrng, 0)) Then With Cells(rowstep, colstep) .Interior.ColorIndex = 45 End With End If End If Next rowstep Next colstep errhandler: Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic Named Range | Excel Discussion (Misc queries) | |||
| Dynamic Named Range | Excel Discussion (Misc queries) | |||
| Dynamic Named Range | Charts and Charting in Excel | |||
| getting the absolute range address from a dynamic named range | Excel Programming | |||
| dynamic named range | Excel Programming | |||
Linear Mode
