Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find duplicate range or cell ref (as precedents) in a cell's formula - an example
No question here, just a procedure example for the achive.
Find duplicate range or cell references (as precedents) in a cell's formula - an example Sub FormulaDuplicateRefCheck() 'Checks each cell's formula in the selection for any duplicate/ multiple reference 'to the same range in the formula Dim c, cell, evalCell, OriginalSelection As Range Dim acFormula, cAddress, FoundRange As String Dim CountCharacter, I As Integer ' On Error Resume Next Set OriginalSelection = Selection 'Loop through each cell in the selection For Each evalCell In OriginalSelection On Error Resume Next 'Turn the evaluated cell's formula into a string acFormula = evalCell.Formula 'Turn the evaluated cell's precedent(s) address references into into a string acPrecAddress = evalCell.Precedents.Address(RowAbsolute:=False, ColumnAbsolute:=False) 'Select the evaluated cell's precedent(s) address references evalCell.Precedents.Select ' Loop through each cell in the evaluated cell's precendents cells For Each c In Selection 'Turn each precedent cell address into a string cAddress = c.Address(RowAbsolute:=False, ColumnAbsolute:=False) 'compare the precedent cell address to the evaluated cell's formula as a string 'to indentify the number of occurrences of the precedent cell's reference For I = 1 To Len(acFormula) If Mid(acFormula, I, Len(cAddress)) = cAddress Then CountCharacter = CountCharacter + 1 End If Next 'if there is more than one occurence then add the precedent cell address to 'a string list If CountCharacter = 2 Then FoundRange = FoundRange & vbLf & cAddress End If CountCharacter = 0 Next c 'Test for existence of items in the multiple reference string list If Len(FoundRange) = 1 Then MsgBox "Cell " & evalCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) _ & " has duplicate range reference(s): " & FoundRange End If FoundRange = "" Next evalCell OriginalSelection.Select End Sub Note: the above code doesn't identify any duplicate Named Ranges. It only works with standard cell range references. Also: Sub FormulaINCONSISTENCYCheck() 'Check a range of cells to see if their formulas are consistent 'when compared amongst themselves 'Consistent formulas in the region must reside to the left and 'right or above and below the cell containing the inconsistent 'formula for the InconsistentFormula property to work properly. Dim c As Range For Each c In Selection ' Perform check to see cell has an inconsistent formula If c.Errors.Item(xlInconsistentFormula).Value = True Then MsgBox "cell " & c.Address & " has an inconsistent formula" End If Next c End Sub seach criteria: precedent duplicate check formula reference duplication duplicate cell references check duplicate precedents exist range reference duplication formula precedents audit range or cell reference duplication error formula inconsistent test |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find duplicate range or cell ref (as precedents) in a cell's formu
Why declare so many of your variables as variant?
Why do you think duplicate cell references are inconsistent? Do you have limited experience with formulas? =if(iserror(match(cell,rng,0),"",vlookup(cell,rng, 2,false)) would be flagged as inconsistent. It incorrectly flags something as simple as =SUM(B1:AB1) as inconsistent (it isn't) yet it would miss =Sum($B$1:B1) (although that isn't necessarily inconsistent - according to your rules, it is and is missed). Hopefully these comments will help others pass by a bad piece of code although it isn't clear why anyone would need this functionality. another one for your personal "hall of shame" <g -- Regards, Tom Ogilvy " wrote: No question here, just a procedure example for the achive. Find duplicate range or cell references (as precedents) in a cell's formula - an example Sub FormulaDuplicateRefCheck() 'Checks each cell's formula in the selection for any duplicate/ multiple reference 'to the same range in the formula Dim c, cell, evalCell, OriginalSelection As Range Dim acFormula, cAddress, FoundRange As String Dim CountCharacter, I As Integer ' On Error Resume Next Set OriginalSelection = Selection 'Loop through each cell in the selection For Each evalCell In OriginalSelection On Error Resume Next 'Turn the evaluated cell's formula into a string acFormula = evalCell.Formula 'Turn the evaluated cell's precedent(s) address references into into a string acPrecAddress = evalCell.Precedents.Address(RowAbsolute:=False, ColumnAbsolute:=False) 'Select the evaluated cell's precedent(s) address references evalCell.Precedents.Select ' Loop through each cell in the evaluated cell's precendents cells For Each c In Selection 'Turn each precedent cell address into a string cAddress = c.Address(RowAbsolute:=False, ColumnAbsolute:=False) 'compare the precedent cell address to the evaluated cell's formula as a string 'to indentify the number of occurrences of the precedent cell's reference For I = 1 To Len(acFormula) If Mid(acFormula, I, Len(cAddress)) = cAddress Then CountCharacter = CountCharacter + 1 End If Next 'if there is more than one occurence then add the precedent cell address to 'a string list If CountCharacter = 2 Then FoundRange = FoundRange & vbLf & cAddress End If CountCharacter = 0 Next c 'Test for existence of items in the multiple reference string list If Len(FoundRange) = 1 Then MsgBox "Cell " & evalCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) _ & " has duplicate range reference(s): " & FoundRange End If FoundRange = "" Next evalCell OriginalSelection.Select End Sub Note: the above code doesn't identify any duplicate Named Ranges. It only works with standard cell range references. Also: Sub FormulaINCONSISTENCYCheck() 'Check a range of cells to see if their formulas are consistent 'when compared amongst themselves 'Consistent formulas in the region must reside to the left and 'right or above and below the cell containing the inconsistent 'formula for the InconsistentFormula property to work properly. Dim c As Range For Each c In Selection ' Perform check to see cell has an inconsistent formula If c.Errors.Item(xlInconsistentFormula).Value = True Then MsgBox "cell " & c.Address & " has an inconsistent formula" End If Next c End Sub seach criteria: precedent duplicate check formula reference duplication duplicate cell references check duplicate precedents exist range reference duplication formula precedents audit range or cell reference duplication error formula inconsistent test |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find duplicate range or cell ref (as precedents) in a cell's formu
Well put Mr. Ogilvy,
This explains why the aztecs are no longer with us. They had a brainsurgeon. Regards, Alan "Tom Ogilvy" wrote in message ... Why declare so many of your variables as variant? Why do you think duplicate cell references are inconsistent? Do you have limited experience with formulas? =if(iserror(match(cell,rng,0),"",vlookup(cell,rng, 2,false)) would be flagged as inconsistent. It incorrectly flags something as simple as =SUM(B1:AB1) as inconsistent (it isn't) yet it would miss =Sum($B$1:B1) (although that isn't necessarily inconsistent - according to your rules, it is and is missed). Hopefully these comments will help others pass by a bad piece of code although it isn't clear why anyone would need this functionality. another one for your personal "hall of shame" <g -- Regards, Tom Ogilvy " wrote: No question here, just a procedure example for the achive. Find duplicate range or cell references (as precedents) in a cell's formula - an example Sub FormulaDuplicateRefCheck() 'Checks each cell's formula in the selection for any duplicate/ multiple reference 'to the same range in the formula Dim c, cell, evalCell, OriginalSelection As Range Dim acFormula, cAddress, FoundRange As String Dim CountCharacter, I As Integer ' On Error Resume Next Set OriginalSelection = Selection 'Loop through each cell in the selection For Each evalCell In OriginalSelection On Error Resume Next 'Turn the evaluated cell's formula into a string acFormula = evalCell.Formula 'Turn the evaluated cell's precedent(s) address references into into a string acPrecAddress = evalCell.Precedents.Address(RowAbsolute:=False, ColumnAbsolute:=False) 'Select the evaluated cell's precedent(s) address references evalCell.Precedents.Select ' Loop through each cell in the evaluated cell's precendents cells For Each c In Selection 'Turn each precedent cell address into a string cAddress = c.Address(RowAbsolute:=False, ColumnAbsolute:=False) 'compare the precedent cell address to the evaluated cell's formula as a string 'to indentify the number of occurrences of the precedent cell's reference For I = 1 To Len(acFormula) If Mid(acFormula, I, Len(cAddress)) = cAddress Then CountCharacter = CountCharacter + 1 End If Next 'if there is more than one occurence then add the precedent cell address to 'a string list If CountCharacter = 2 Then FoundRange = FoundRange & vbLf & cAddress End If CountCharacter = 0 Next c 'Test for existence of items in the multiple reference string list If Len(FoundRange) = 1 Then MsgBox "Cell " & evalCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) _ & " has duplicate range reference(s): " & FoundRange End If FoundRange = "" Next evalCell OriginalSelection.Select End Sub Note: the above code doesn't identify any duplicate Named Ranges. It only works with standard cell range references. Also: Sub FormulaINCONSISTENCYCheck() 'Check a range of cells to see if their formulas are consistent 'when compared amongst themselves 'Consistent formulas in the region must reside to the left and 'right or above and below the cell containing the inconsistent 'formula for the InconsistentFormula property to work properly. Dim c As Range For Each c In Selection ' Perform check to see cell has an inconsistent formula If c.Errors.Item(xlInconsistentFormula).Value = True Then MsgBox "cell " & c.Address & " has an inconsistent formula" End If Next c End Sub seach criteria: precedent duplicate check formula reference duplication duplicate cell references check duplicate precedents exist range reference duplication formula precedents audit range or cell reference duplication error formula inconsistent test |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a cell's content in another cell but only as whole word(s) | Excel Worksheet Functions | |||
How to find a cell's location/index within a range? | Excel Programming | |||
Trace Precedents and dependents for a range of cell ? | New Users to Excel | |||
how do i find and replace a portion of a cell's formula? | Excel Discussion (Misc queries) | |||
where can I find the trace precedents button on the toolbar | Excel Programming |