VBA: Going to the next cell

[mykulpasskwa]

I have a range starting at B4 with term codes grouped together year and I'm trying to add the values that start with the same 4 digit year code. So 2019 should be 28,000; 2020 should be 28,500; and 2021 should be 6 (test sample). The idea is the user selects a cell and VBA will add that year together. If they select another year, the result is for that year. The issue I'm having right now is that it's adding 201910 and 201930, but it's skipping 201950, and it's only totaling to 25000 instead of 28000. I seem to have something out of order or I'm not using something correctly. Any ideas?

Sub Sum_by_Month_and_Year()
    'declare variables
    Dim ws As Worksheet
    Dim output As Range
    Dim Lresult As String
    Dim yearvalue As String
    Dim counter As Long
    Dim f As Integer
    Dim l As Integer 'lowercase L
    Dim X As Integer
        Set ws = Workbooks("Macro OG Recon Calculator - SANDBOX.xlsm").ActiveSheet
        Set output = ws.Range("F150")
        
        counter = 0
        'sum value by year
        f = ActiveCell.Row
        l = Range("B4").End(xlDown).Row
        For X = f To l
                Lresult = Left(ActiveCell.Value, 4)
                yearvalue = Left(ActiveCell.Offset(1, 0).Value, 4)
                
                If Lresult = yearvalue Then
                    counter = counter + ws.Range("F" & X)
                    ActiveCell.Offset(1, 0).Select
                End If
            Next X
        output = MsgBox(counter)
        ws.Range("F150").Value = ""
End Sub

 

[Fluff]

Why not just use a formula, like

+Fluff New.xlsm
	A	B	C	D	E	F	G	H
1
2								2019
3
4		201910				10000		28000
5		201930				15000
6		201950				3000
7		202010				100
8		202020				11
9
Main

Cell Formulas
Range	Formula
H4	H4	=SUMIFS(F4:F8,B4:B8,">="&H2*100,B4:B8,"<"&(H2+1)*100)

Input the year into H2

 

[mykulpasskwa]

Why not just use a formula,

I'm working on a bigger project overall, and this is just one piece I need to get working before I move onto the next step. The spreadsheet already takes up more space than I want it to, but more importantly I want it to notify the user how much of their benefits need to be reported and how much they need to find alternate funding for. There will be more information than that as well, but those are critical points where mistakes happen in our job.

 

[Fluff]

Which cell would the user be selecting, before running the code?

 

[mykulpasskwa]

Which cell would the user be selecting, before running the code?

Well, for now, they have to select the first code of that year group. So, if they want all of 2019 they have to select 201910. If they want 2020 they have to select 202010.

I'm eventually hoping to work out where the user can select any cell in that year group and vba will find all the years that match that selection. Just to make it more flexible and user friendly.

 

[Fluff]

How about

Sub Sum_by_Month_and_Year()
    'declare variables
    Dim ws As Worksheet
    Dim output As Range
    Dim Lresult As String
    Dim yearvalue As String
    Dim counter As Long
    Dim f As Integer
    Dim l As Integer 'lowercase L
    Dim X As Integer
        Set output = Range("f150")
        
        counter = 0
        'sum value by year
        f = ActiveCell.Row
        l = Range("B4").End(xlDown).Row
        Lresult = Left(Cells(f, 2), 4)
        For X = f To l
            If Left(Cells(X, 2), 4) = Lresult Then
               counter = counter + Range("F" & X)
            End If
         Next X
        output = counter
        
End Sub

 

[mykulpasskwa]

How about

Yeah, that works great! Thank you so much. I've been sitting here messing around with it and that definitely got me through the part I'm stuck on. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.