Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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" wrote: 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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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 On Fri, 17 Jul 2009 22:55:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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" wrote: 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 On Fri, 17 Jul 2009 22:55:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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 On Sat, 18 Jul 2009 22:18:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 22:55:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
The code should be in the workbook 2004-2005.xls on either a module sheet or
the Input VBA sheet. "Minitman" wrote: 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 On Sat, 18 Jul 2009 22:18:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 22:55:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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" wrote: 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 On Sat, 18 Jul 2009 22:18:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 22:55:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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 On Sun, 19 Jul 2009 06:11:01 -0700, Joel wrote: 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" wrote: 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 On Sat, 18 Jul 2009 22:18:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 22:55:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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" wrote: 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 On Sun, 19 Jul 2009 06:11:01 -0700, Joel wrote: 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" wrote: 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 On Sat, 18 Jul 2009 22:18:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 22:55:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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 On Sun, 19 Jul 2009 08:06:01 -0700, Joel wrote: 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" wrote: 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 On Sun, 19 Jul 2009 06:11:01 -0700, Joel wrote: 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" wrote: 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 On Sat, 18 Jul 2009 22:18:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 22:55:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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" wrote: 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 On Sun, 19 Jul 2009 08:06:01 -0700, Joel wrote: 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" wrote: 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 On Sun, 19 Jul 2009 06:11:01 -0700, Joel wrote: 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" wrote: 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 On Sat, 18 Jul 2009 22:18:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 22:55:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Validation List for Matching Entries at Start-up
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 On Sun, 19 Jul 2009 12:14:01 -0700, Joel wrote: 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" wrote: 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 On Sun, 19 Jul 2009 08:06:01 -0700, Joel wrote: 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" wrote: 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 On Sun, 19 Jul 2009 06:11:01 -0700, Joel wrote: 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" wrote: 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 On Sat, 18 Jul 2009 22:18:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 22:55:01 -0700, Joel wrote: 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" wrote: 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 On Fri, 17 Jul 2009 13:30:01 -0700, Joel wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check Spreadsheet entries against a list | Excel Discussion (Misc queries) | |||
Check for duplicate entries in pull-down list | Excel Discussion (Misc queries) | |||
View all entries in Validation List | Excel Discussion (Misc queries) | |||
Matching List Entries | Excel Discussion (Misc queries) | |||
How many entries can you have in List Data Validation | Excel Programming |