VBA: Find and Group All Occurrences of Value, Run Sub, Move to Next Group

[mykulpasskwa]

Hi, I need some help getting started on a code. I have a range ("B4:B") of variables that are related by the first four characters (the year). Currently an input box will appear asking which year to run the code in, and the default value is based on the left four characters of the active cell. The years are usually grouped together so I currently have it going to the next cell and checking if the new active cell matches the previous cell. I would like to eliminate the input box completely and:

1. have Excel start at "B4" which contains the first term of that year
2. find all occurrences of that year,
3. run the sub I have already which sums values in an adjacent cell and highlights the term they went over an annual cap
4. then moves on to the next cell
   1. if it is the same year it adds that value to the counter
   2. if it is a new year it resets the counter and runs the third procedure above

The terms look like something like [201510, 201530, 201540]; [201615, 201625]...[202010, 202030, 202050] where the last two digits don't matter and the first four digits are the year. I've been poking around the internet and I'm not sure whether to use the Find method or if I need to create a dictionary (which I've never done). There might be another solution like a For loop, but the main thing I need to figure out is how to make excel take the first occurrence of four digits, find the other cells with those same four digits, and then when it comes to a different year it finds cells with that year, and so on until it reaches a blank cell. I'm not going to include the sub I have just yet because I don't know that it's related to solving this particular problem, but I'll be happy to provide it later if requested. I'm hoping someone can tell me the best method to get started based on the information provided, and I can do further research from there. I'm not expecting anyone to write code for me although it is welcomed and appreciated.

 

[DanteAmor]

Let's start with this, the following macro reads the data from column B and accumulates the values from column C, puts the result in E sums per year (4 digits).

varios 12jun2020.xlsm
	A	B	C	D	E	F
1
2
3		YearNN	Value		Results
4		201510	100		2015	331
5		201520	130		2016	601
6		201530	101		2017	304
7		201615	131
8		201625	102
9		201635	132
10		201645	103
11		201655	133
12		201750	104
13		201760	200
Hoja5

Sub TestDic()
  Dim arr As Variant, i As Long, dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
  'stores in an array all the data from columns B and C,
  'starting at B4 and up to the last row with data from column C.
  arr = Range("B4", Range("C" & Rows.Count).End(3)).Value2
  
  'cycle from 1 to the last value of array (a)
  For i = 1 To UBound(arr)
    'key(año), arr(i,1). i is the row of the array
    '1 is the column 1 of the array, represents column B
    '  = the value contained within the key + (acumulate) arr(i, 2) 2 represents column C
    dic(Left(arr(i, 1), 4)) = dic(Left(arr(i, 1), 4)) + arr(i, 2)
  Next
  
  Range("E4").Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.items))
End Sub

 

[mykulpasskwa]

I can't test it right now unfortunately, but that looks so much more elegant than what I was working on this afternoon. I think I can definitely work with that, but I'll check it tomorrow and let you know how it turns out. Thank you so so much for going through all that trouble!

 

[mykulpasskwa]

Let's start with this, the following macro reads the data from column B and accumulates the values from column C, puts the result in E sums per year (4 digits).

Sorry for the delayed response. I wasn't able to get back to my work computer over the weekend. That worked perfectly though. I'm going to modify it a bit to fit our needs, but that's just what I was looking to try and do for now. Thank you also for the detailed explanation!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.