Search code examples
vbaexcelcontiguous

Counting Cells Non-Contiguously

I'm not entirely sure how to express what I am attempting to do, but I will do the best I can.

I'm wanting to count non-contiguous cells in Excel in the order which they are clicked.

For instance, lets say that column A has a drop down and I want column B to record in what order the column A drop down was selected. So if a person were to skip past A1:A3 and select A4 then B4 would equal 1; next they select A2 then set B2=2.

Is this at all possible? I've tried indexing, vlookups, counts, helper cells, and nothing seems to work since I'm assuming Excel thinks in a sequential manner and what I'm doing is trying to account for seemly random non-sequential human interaction.

Disabling iterative calculation is also not an option as there are other references that rely on iterations.

I know VBA, but only very lightly. Any help on would be greatly appreciated. Thank you.

Solution

  • Place the following Event macro in the worksheet code area:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, wf As WorksheetFunction
    Dim B As Range

    Set A = Range("A:A")
    Set B = Range("B:B")
    Set wf = Application.WorksheetFunction
    If Intersect(A, Target) Is Nothing Then Exit Sub
    If Target.Count <> 1 Then Exit Sub
    Application.EnableEvents = False
        Target.Offset(0, 1).Value = wf.Max(B) + 1
    Application.EnableEvents = True
End Sub

    Because it is worksheet code, it is very easy to install and automatic to use:

    1. right-click the tab name near the bottom of the Excel window
    2. select View Code - this brings up a VBE window
    3. paste the stuff in and close the VBE window

    If you have any concerns, first try it on a trial worksheet.

    If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

    To remove the macro:

    1. bring up the VBE windows as above
    2. clear the code out
    3. close the VBE window

    To learn more about macros in general, see:

    http://www.mvps.org/dmcritchie/excel/getstarted.htm

    and

    http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

    To learn more about Event Macros (worksheet code), see:

    http://www.mvps.org/dmcritchie/excel/event.htm

    Macros must be enabled for this to work!