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

mykulpasskwa

Board Regular
Joined
Mar 20, 2018
Messages
65
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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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
ABCDEF
1
2
3YearNNValueResults
42015101002015331
52015201302016601
62015301012017304
7201615131
8201625102
9201635132
10201645103
11201655133
12201750104
13201760200
Hoja5


VBA Code:
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

Board Regular
Joined
Mar 20, 2018
Messages
65
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

Board Regular
Joined
Mar 20, 2018
Messages
65
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,709
Messages
5,573,735
Members
412,550
Latest member
soking
Top