Home / User Contributed Macros / Increment counter if present else add it to table

Increment counter if present else add it to table

The following macro increments the count in Column E, If the value in Range 'A1' is present in Range(D1:D100); Else the value is added in Column D
  2. Public Sub addToTable()
  3.  
  4.     Dim searchItem as String
  5.     searchItem = Range("A1")    'Input Range
  6.  
  7.     Dim searchRegion,count as Range
  8.     Set searchRegion = Range("D1:D100")      'Table Range
  9.  
  10.     Dim nextEmptyRow as Integer
  11.  
  12.     For each cell in searchRegion
  13.         If(ucase(cell.value) = ucase(searchItem))    Then

  15.         'Increment the count if the value is present and exit
  16.             set count = Cells(cell.row, cell.column+1)           
  17.             count.value = count.value + 1

  19.             Exit Sub
  20.         End If
  21.     Next cell
  22.  
  23.     nextEmptyRow = searchRegion.End(xlDown).Row
  24.  
  25.     'Inserting new value to the table if the value is not present    
  26.     cells(nextEmptyRow+1,1).EntireRow.Insert
  27.     cells(nextEmptyRow+1, searchRegion.column).value = searchItem
  28.     cells(nextEmptyRow+1, searchRegion.column+1).value = 1
  29.  
  30. End Sub


    		 RSS of this page