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

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


"[email protected]" 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
>
>

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
>
>
> "[email protected]" 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
> >
> >

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?


[email protected] 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
> >
> >
> > "[email protected]" 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
> > >
> > >

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


[email protected] 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?
>
>
> [email protected] 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
> > >
> > >
> > > "[email protected]" 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
> > > >
> > > >

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
>
>
> [email protected] 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?
> >
> >
> > [email protected] 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
> > > >
> > > >
> > > > "[email protected]" 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
> > > > >
> > > > >