Excel Howto?
Excel VBA how to?

-Aviral Mittal




1. How to report repeat values in a column?
Sometimes a repeated value in a Colum isn't desired.
A function is presented below which will report if a value has been repeated in a column.
Following this function, 2 Subroutines are presented, first does not report an error, as there is no repetition,
and the second reports an error, as "London" is repeated in Column B (2) as shown below:

This function can also take in Optionally the Sheet Name as input, to search in a named sheet!
The search Column Number is also Optional. If not given, it defaults to 1.




Function FindRowAddr(RepeatString As String, Optional colNum As Long, Optional shtName As String) As String
'The function reports multiple occurences of 'RepeatString' as error.
'Example 1 : colNum = FindRowAddr("London", 1, "Sheet1"), where colNum is of type long
'Example 2 : colNo = FindRowAddr(RepeatString:="Paris", RowNum:=1, shtName:="Lists")
'Example 3 : colNo = FindRowAddr(RepeatString:="Zurich", shtName:="Lists")
'Finds only 1 occurence of the RepeatString, if more are present, it reports error
  Dim sht As Worksheet
  Dim mCell As Range
 
  FindRowAddr = "$0$0" 'default return value.
  If (shtName = "") Then
    shtName = "Sheet1"
  End If
  On Error GoTo errorHandler
  Set sht = ThisWorkbook.Worksheets(shtName)
 
 
  sht.Activate
  If colNum = 0 Then
    colNum = 1
  End If
 
  Set mCell = Columns(colNum).Find(What:=RepeatString, lookat:=xlWhole) 'searchStr is the string which is being searched
  If mCell Is Nothing Then
    Debug.Print "ERROR! From Function 'FindRowAddr': RepeatString " & "'" & RepeatString & "'" & " not found in sheet '" & shtName & "'"
    MsgBox "ERROR! From Function 'FindRowAddr': RepeatString " & "'" & RepeatString & "'" & " not found in sheet '" & shtName & "'"
    Exit Function
  Else
    FindRowAddr = mCell.Address
    'Debug.Print "INFO: From Function 'FindRowAddr': Found '" & RepeatString & "' Address = " & mCell.Address & " In Sheet '" & shtName & "'"
  End If
 
  Dim firstCellAddress As String
  firstCellAddress = mCell.Address
  Dim ii As Long
  ii = 0
  Do
    ii = ii + 1
    If (ii > 1) Then
      Debug.Print "ERROR! Sheet " & "'" & shtName & "'" & " has multiple occurence of " & "'" & RepeatString & "' at: " & mCell.Address
      MsgBox "ERROR! Sheet " & "'" & shtName & "'" & " has multiple occurence of " & "'" & RepeatString & "' at: " & mCell.Address
      FindRowAddr = "$0$0"
    End If
    Set mCell = Columns(colNum).FindNext(mCell)
  Loop While firstCellAddress <> mCell.Address

  Exit Function
errorHandler:
  Debug.Print "ERROR! From Function 'FindRowAddr': Sheet " & "'" & shtName & "'" & " Does not exist"
  MsgBox "ERROR! From Function 'FindRowAddr': Sheet " & "'" & shtName & "'" & " Does not exist"

End Function

Sub TryReportRepeatValue()
    'This will search in Col 1 and will not report an error.
    Dim RepeatString As String
    Dim searchColNum As Long
    searchColNum = 1
    RepeatString = "London"
    Dim iRowAddr As Long
    'the following will try to find "London" in Colmun 1 of "Sheet"
    iRowAddr = Split(FindRowAddr(RepeatString, searchColNum, "Sheet1"), "$")(2)
    If (iRowAddr = 0) Then
      Debug.Print "ERROR_0004: Multiple Init Error Occured"
      Exit Sub
    Else
      MsgBox "Found " & RepeatString & " at Row Number = " & iRowAddr
    End If
End Sub

Sub TryReportRepeatValue_1()
    'This will search in Col 2 and report an error, as "London" is repeated
    Dim RepeatString As String
    Dim searchColNum As Long
    searchColNum = 2
    RepeatString = "London"
    Dim iRowAddr As Long
    'the following will try to find "London" in Colmun 1 of "Sheet"
    iRowAddr = Split(FindRowAddr(RepeatString, searchColNum, "Sheet1"), "$")(2)
    If (iRowAddr = 0) Then
      Debug.Print "ERROR_0004: Multiple Init Error Occured"
      Exit Sub
    Else
      MsgBox "Found " & RepeatString & " & at Row Number = " & iRowAddr
    End If
End Sub
WB with the above code can be downloaded Here:
Book6.xlsm
<- Previous                                                                                                Next ->

Key Words: Excel Find repeat value. How to know if there are multiple values in same column