VBA increment the month plus 1 based on value in adjacent column

[Jak]

Hi Guys

I have data in Columns "B:C" starting from Row 5. The number of rows varies. In Column "B" i have cell values "Default", "Carry Forward". In Column "C" I have dates ie "25/07/2020"

I need some help with VB to identify the text "Default" in Column "B" and ignore the text "Carry Forward". Once identified, the cells offset value in Column "C" needs to have the month incremented by 1.

Example Before
Column A Column B
Default 22/06/2020
Carry Forward 24/06/2020


Example After
Column A Column B
Default 22/07/2020
Carry Forward 24/06/2020

Any help appreciated

Jak

 

[Peter_SSs]

What should happen if a 'Default' date is, say, 31/01/2020?

 

[Jak]

Hi Peter, good point. in that instance it would go to 29/02/2020. likewise if the date was the 31/12/2020 it would go to 31/01/2021

 

[Peter_SSs]

Thanks for the clarification.
Test with a copy of your data

If your data is not very big you could try

Sub Increment_Month_v1()
  Dim c As Range
  
  For Each c In Range("B5", Range("B" & Rows.Count).End(xlUp))
    With c
      If LCase(.Offset(, -1).Value) = "default" And IsDate(.Value) Then .Value = DateAdd("m", 1, .Value)
    End With
  Next c
End Sub

For larger data or if you just want faster code, try

Sub Increment_Month_v2()
  Dim a As Variant
  Dim i As Long
  
  With Range("A5", Range("B" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      If LCase(a(i, 1)) = "default" And IsDate(a(i, 2)) Then a(i, 2) = DateAdd("m", 1, a(i, 2))
    Next i
    .Value = a
  End With
End Sub

 

[Jak]

Hi Peter

Thanks very much for you help. I was having an issue updating the date. I stepped through the code and changed the range to column C. The code executed and everything except my unwanted changes were made. Just what I needed, thanks again.

Jak

 

[Peter_SSs]

and changed the range to column C.

Ah, yes, I was confused by ..

I have data in Columns "B:C"

Example Before
Column A Column B

Anyway, glad you got it sorted! You are welcome.