-1

I'm working on a table that looks like this:

Last Type | Today Type | N Days
-------------------------------
  Type1   |   Type2    |   0
-------------------------------
  Type1   |   Type1    |   1
-------------------------------
  Type2   |   Type2    |   20   
-------------------------------

Every day the table is updated in "Today Type" with the respective type. The column "N Days" will count the number of days with the last type.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
FT29
  • 11
  • 1
    Please edit your question to describe exactly what the problem is with you code: desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. See: [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). – Jean-François Corbett Apr 02 '15 at 11:11
  • You forgot to post a question. – Reticulated Spline Apr 02 '15 at 11:21

1 Answers1

0

You could use iterative calculation option in excel, but I'm not a big fan of this and for me the task seems to be well suited to use a Worksheet_change event handler.

Try inserting such code in your worksheet code - not standard module (so right-click on sheet name tab and ->show code):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim mycell As Range
If Not Intersect(Target, Columns(2)) Is Nothing Then
  For Each mycell In Intersect(Target, Columns(2))
    If mycell.Value = mycell.Offset(0, -1).Value Then
      mycell.Offset(0, 1).Value = mycell.Offset(0, 1).Value + 1
    Else
      mycell.Offset(0, 1).Value = 0
      mycell.Offset(0, -1).Value = mycell.Value
    End If
  Next mycell
End If
End Sub

Remember to save your workbook in macro-containing format like xlsm or xls The code above is for the data in columns A and B (previous and current) and counter in C. Instead of Columns(2) in real life it's probably better to use Range("B2:B10") or similar (it appears twice in above code).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kaper
  • 11
  • 2
  • Thank you Kaper! That's exactly what I needed!!! :) I don't have enough rep to vote up sorry... – FT29 Apr 02 '15 at 15:21
  • No problem, I'm glad it worked. If you wish, you can check for reputation possibilities (by the way it's easy to post attachments there) on excelforum.com - see my profile: http://www.excelforum.com/members/682378.html – Kaper Apr 04 '15 at 19:44