Search code examples
excelexcel-udfvba

How to count the number of a value?

I am dealing with 956 values in a row. I have a problem in getting the row distances in each value because there are too many of them. Is there a way to achieve this using Excel-VBA with a button?

For example: This shows the number of times where the distance of a row is counted.

enter image description here

Solution

  • Create a user-defined function in a module, with this code:

    Option Explicit

Public Function Distance(matrix As Range, value As Long, rowCount As Long)
    Dim cell As Range
    Dim lastRow As Long
    Dim result As Long

    lastRow = 1
    For Each cell In matrix
        If cell.value = value Then
            If cell.row - lastRow + 1 = rowCount Then result = result + 1
            lastRow = cell.row
        End If
    Next
    Distance = result
End Function

    That is all the code you need. Now in the Sheet put this formula in cell I5, i.e. the top-left cell of your results table:

    =Distance($A$1:$F$11, $H5, I$4)

    Depending on how many rows are in your source table on the left, you may need to adjust the 11 in that formula to some larger number, so the whole table is referenced there.

    Then copy this formula to the rest of your output table: drag/copy horizontally, then select that row, and drag/copy downwards.

    The table will fill with lots of zeroes as well. To get rid of those, use cell formatting. Use this custom format:

    0;-0;

    This is the result (ignore that you see semi-colons in the formula; those are related to my regional settings):

    enter image description here

    There is no need of a button. The results are updated like any other standard formula would do.

    Note that you could name your function with another name, if you want, but make sure to do the same change in the formulas you have in the sheet.