Check Validation List for Matching Entries at Start-up

[Minitman]

Greetings,

I am using Excel from Office 2003 on an XP box.

I have several workbooks that use a drop down validation list to pick
name from an other workbook of names. It is working fine. The
problem is that the workbook of names called MCL.xls is being updated
due to errors in the list. When an entry in MCL.xls changes, the drop
down validation list no longer matches the value in the validation
drop down cell.

I need a way to identify these unmatched entries when I open each so
that I can manually find the new corrected entry from the drop down
list.

Anyone have any ideas?

Any help will be appreciated.

-Minitman

 

[Joel]

The code below uses the special cells method to find each validation list on
the worksheet. Then checks to see if the valkue in the cell matches the
validation list. The validatioin list has an equal sign in front of the
address range so I remove it wih the mid statement. Finally I use a FIND
method for checking if the data in he cell matches any value in the
validation list. If it doesn't a highlight the cell.

Sub test()

With Sheets("Sheet1")
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation)
For Each cell In Validatecells
'remove equal sign from formula
validationRange = Mid(cell.Validation.Formula1, 2)
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub

 

[Minitman]

Hey Joel,

Thanks for the reply.

I could not get this code to work. I tried to follow your code but I
could not understand it.

It appears that I need to give a bit more information.

The column that has the validation is a named range called
rInputRefName.

The validation list has the formula: =RefName a dynamic named range
rerouted to the master customer list or MCL.xls.

What I need to do is check each cell in rInputRefName to see if there
is a match in the RefName list and if there is no exact match then
color that cell. If there is a match in RefName go to the next cell
and repeat the code.

Any other ideas?

-Minitman

 

[Minitman]

Hey Joel,

Thanks for the reply.

I could not get this code to work. I tried to follow your code but I
could not understand it.

It appears that I need to give a bit more information.

The column that has the validation is a named range called
rInputRefName.

The validation list has the formula: =RefName a dynamic named range
rerouted to the master customer list or MCL.xls.

What I need to do is check each cell in rInputRefName to see if there
is a match in the RefName list and if there is no exact match then
color that cell. If there is a match in RefName go to the next cell
and repeat the code.

Any other ideas?

-Minitman

 

[Joel]

The code I provide should work under you case provide the following

1) You changed the sheet name to match the worksheet were the validation
cells are located.
2) The worksheet where the validation cells are located only has these
validation cells and no other.
3) The macro is located in the same workbook as the validation cells (not
MCL.xls )

What errors are you getting? ARe any cells being highlighted?

I reposted the code with comments

Sub test()

'sheet with validation cells
With Sheets("Sheet1")
'get all the validation cells
Set Validatecells = .Cells.SpecialCells(Type:=xlCellTypeSameValidation)
'check each validation cells
For Each cell In Validatecells
'remove equal sign from validation formula
validationRange = Mid(cell.Validation.Formula1, 2)
'compare data in the validation cell with the range of cells in MCL.xls
Set c = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
'if there is no match highlight the cell
If c Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub

 

[Minitman]

Hey Joel,

Sorry about the duplicate reply.

The first thing I did was to dim the variables including "cell".

I set up a CommandButton to run your code.

I changed the sheet name to the actual sheet name.

This is what I came up with. Do you see any errors (other word wrap
problems)?
_____________________________________
Private Sub CommandButton1_Click()
Test
End Sub

Sub Test()
Dim Validatecells
Dim validationRange
Dim C
Dim cell

'sheet with validation cells
With Sheets("Input")
'get all the validation cells
Set Validatecells = _
.Cells.SpecialCells(Type:=xlCellTypeSameValidation)
'check each validation cells
For Each cell In Validatecells
'remove equal sign from validation formula
validationRange = Mid(cell.Validation.Formula1, 2)
'compare data in the validation cell with the range of cells in
'MCL.xls
Set C = Range(validationRange).Find(what:=cell.Value, _
LookIn:=xlValues, lookat:=xlWhole)
'if there is no match highlight the cell
If C Is Nothing Then
cell.Interior.ColorIndex = 3
End If
Next cell

End With

End Sub
_____________________________________

I get this error:
_____________________________________

Run-time error '1004':
No cells were found.
_____________________________________

Then debug high lights this line:

Set Validatecells = _
.Cells.SpecialCells(Type:=xlCellTypeSameValidation)

Any ideas?

-Minitman

 

[Joel]

The input sheet doesn't have a cells with validation lists. Do you have more
htan one workbook that your working with? Is Input sheet in the same
workbook as the macro? Is the macro in a module VBA sheet?

 

[Minitman]

There are two workbooks - MCL.xls and 2004-05.xls. MCL has the list
of names and 2004-05 has the column with the validation cells (most of
which are empty).

The macro is in the code section of the worksheet. I am using a
CommandButton to trigger the code.

-Minitman

 

[Joel]

The code should be in the workbook 2004-2005.xls on either a module sheet or
the Input VBA sheet.

 

[Joel]

I didn't finish my message

I just ran the code below on a new workbook with a sheet called Input.
First I didn't have any validation list and got the error 1004. Then I added
a validaion list and didn't get the error. This indicates the code was
exeercising the workbook with worksheet Input otherwise you would get an
error on the WITH statement. Do you have any On Error statments? Comment
them out it may be masking a different error.

sub test()
With Sheets("Input")
Set Validatecells = _
.Cells.SpecialCells(Type:=xlCellTypeSameValidation)
End With
End Sub

 

[Minitman]

Just for clarification, 2004-05.xls in this case means the workbook
for May, 2004.

I changed locations and added your new code into Module1 (I changed
the name of the old code to Test1 so that it would not be called)

I could not find any "On Error Resume Next" statements, but I put an
"On Error GoTo 0" in front of this code just in case. This made no
difference either rem'd pr not.

I still get the same error message.

How did you build your validation list?

My validation list is in Column D Rows 3:837 (Also named
"rInputRefName"). It is referring to the named range "RefName" as
=RefName. Named range "RefName" refers to a dynamic named in MCL.xls
known as "MCL_Name" with this formula: =MCL.xls!MCL_Name

I even moved it to a workbook with data in the area with the
validation applied and still the same error message.

What am I doing wrong?

-Minitman

 

[Joel]

I creeated a new workbook. Then named one sheet "Input". I next selected a
cell and went to Data - Validation and selected List. Then in the address
box in the dialog I selected a range of address and pressed OK.

Finally I put my simple macro into VBA. I didn't get the 1004 error.

 

[Minitman]

Thanks Joel for that clarification..

Any idea's as to why it wont work for named ranges locally or dynamic
named ranges on MCL.xls?

I am a little confused as to why we are looking for which cells have
validation, since that is a know range called rInputRefName. Couldn't
we just as easily use that name range instead of trying to find out
what it is?

-Minitman

 

[Joel]

The code works with named ranges on my PC. I wrote this code before you said
you were using named ranges and the code should work either way.

I usiing Range("mystring") in the code.

I could of used

for each cell in range(rInputRefName)

instead of

For Each cell In Validatecells

 

[Minitman]

It is not just named ranges, it is also a named range referencing a
dynamic named range in another workbook (This has caused problems
before), I did however, find some information on Match. This did
work when I did as you suggested and placed it in a code Modula
instead of the sheet code section.

Here is that code:
_____________________________________
Sub CheckForBadReferences()
Dim C
Dim rSourceRange As Range
Dim res As Variant
Set rSourceRange = Sheets("Input").Range("rInputRefName")
For Each C In rSourceRange.Cells
res = Application.Match(C.Value, Range("RefName"), 0)
Select Case C.Value
Case vbNullString, 0, ""
C.Interior.ColorIndex = xlNone
Case Else
If IsError(res) Then
C.Interior.ColorIndex = 3
Else
C.Interior.ColorIndex = xlNone
End If
End Select
Next
End Sub
________________________________________

Using Match and checking if there is a match or an error is something
I saw in a post telling someone how to match dates - this is simpler.

Again thanks for your help.

-Minitman