VBA to run routine based on month in corresponding cell

[EssKayKay]

Hello,

I’m looking for a VBA routine that checks if the month of a date in the corresponding row is December; if so, run another routine. Column “C” contain dates; Column “M” is where the entry to be checked will be entered. I assume I want to place the code in the Worksheet_SelectionChange sub.

As example – if an entry is made in cell M44, then check if the month of the date in C44 is 12 (i.e., December). If it is, then run a subroutine called DistributePITI().

Thanks for viewing,
Steve K.

 

[Joe4]

OK, can you tell me exactly what is in cell C36?

 

[EssKayKay]

Column C is dates with a long formula:

=IF(B36="","",IF(OR(periods_per_year=26,periods_per_year=52),IF(periods_per_year=26,IF(B36=1,fpdate,C35+14),IF(periods_per_year=52,IF(B36=1,fpdate,C35+7),"n/a")),IF(periods_per_year=24,DATE(YEAR(fpdate),MONTH(fpdate)+(B36-1)/2+IF(AND(DAY(fpdate)>=15,MOD(B36,2)=0),1,0),IF(MOD(B36,2)=0,IF(DAY(fpdate)>=15,DAY(fpdate)-14,DAY(fpdate)+14),DAY(fpdate))),IF(DAY(DATE(YEAR(fpdate),MONTH(fpdate)+B36-1,DAY(fpdate)))<>DAY(fpdate),DATE(YEAR(fpdate),MONTH(fpdate)+B36,0),DATE(YEAR(fpdate),MONTH(fpdate)+B36-1,DAY(fpdate))))))

under the current data, cell C36 has a date of 2/1/2025; C35=1/1/2025; C37=3/1/2025; etc.

 

[Joe4]

See if this makes any difference (I added another level of checking):

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim rng As Range
    
'   Set up error handling
    On Error GoTo err_chk
   
'   Check for entry made to column M
    Set rng = Intersect(Target, Columns("M:M"))
   
'   If not entry made in column M, exit
    If rng Is Nothing Then Exit Sub
   
'   Loop through new entries made in column M
    For Each cell In rng
'       Check month of date in column C
        If (cell.Offset(0, -10) <> "") And IsDate(cell.Offset(0, -10)) Then
            If Month(cell.Offset(0, -10)) = 12 Then
'               Call procedure
                Call DistributePITI
            End If
        End If
    Next cell
    
    Exit Sub
    
'error handling
err_chk:
    MsgBox Err.Number & ":" & Err.Description & vbCrLf & "Error happening on row: " & cell.Row

End Sub

If you still get the error, temporarily comment out your procedure call line, and add a message box like this (changes in red):

'       Check month of date in column C
        If (cell.Offset(0, -10) <> "") And IsDate(cell.Offset(0, -10)) Then
            If Month(cell.Offset(0, -10)) = 12 Then
'               Call procedure
                'Call DistributePITI
                MsgBox "Condition is met!"
            End If
        End If

If that runs without error, that tells me the error is probably with your "DistributePITI" procedure. I don't think you posted the code for that, so I am not sure what that is doing.

 

[EssKayKay]

See if this makes any difference (I added another level of checking):

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim rng As Range
   
'   Set up error handling
    On Error GoTo err_chk
  
'   Check for entry made to column M
    Set rng = Intersect(Target, Columns("M:M"))
  
'   If not entry made in column M, exit
    If rng Is Nothing Then Exit Sub
  
'   Loop through new entries made in column M
    For Each cell In rng
'       Check month of date in column C
        If (cell.Offset(0, -10) <> "") And IsDate(cell.Offset(0, -10)) Then
            If Month(cell.Offset(0, -10)) = 12 Then
'               Call procedure
                Call DistributePITI
            End If
        End If
    Next cell
   
    Exit Sub
   
'error handling
err_chk:
    MsgBox Err.Number & ":" & Err.Description & vbCrLf & "Error happening on row: " & cell.Row

End Sub

If you still get the error, temporarily comment out your procedure call line, and add a message box like this (changes in red):

'       Check month of date in column C
        If (cell.Offset(0, -10) <> "") And IsDate(cell.Offset(0, -10)) Then
            If Month(cell.Offset(0, -10)) = 12 Then
'               Call procedure
                'Call DistributePITI
                MsgBox "Condition is met!"
            End If
        End If

If that runs without error, that tells me the error is probably with your "DistributePITI" procedure. I don't think you posted the code for that, so I am not sure what that is doing.

Joe, I think you got it. I ran a couple tests and all looks good. I have more testing to do but this looks really promising. For now, I will call this SOLVED.

Thank you so much Joe for your time and consideration. You guys and gals are so amazing and helpful.
Again - much appreciated,
Steve K.

 

[Joe4]

You are welcome.
Glad I was able to help!