Vba add 1 to number inside text for column

[Fred_111]

Hello there,
I have a worksheet where I'd like to add 1 year to the Date column and add 1 to a number inside some text in another column. The Date column, I have sorted, but the number inside text is the worry. My Col "K" has entries like '12/09/2019' & '17/10/2019'. Col "L" has 'Birthday, 74 yrs' & 'Wedding Anniversary, 58 yrs' I can Evaluate the formula into 'new' cells, but how can I update into the existing cells? My code so far is.. Sub Test_VBA_2() Dim LastRow As Long Dim i As Long With ActiveSheet LastRow = Range("J" & Rows.Count).End(xlUp).Row For i = 4 To LastRow If Range("K" & i) <> "" Then .Range("K" & i).Value = DateAdd("yyyy", 1, .Range("K" & i).Value) ''' I'm trying to do a similar thing for Col "L", add 1 to the number inside text. ''' The formula would be like '=IF(L4<>"",LEFT(L4,(FIND(",",L4)+1))&LEFT(MID(L4,FIND(",",L4)+2,256),2)+1&RIGHT(L4,4),"") ''' I converted the formula to ''' .Range("L" & i).Formula = "=IF(.Range(" & Chr(34) & "L" & Chr(34) & " & i)<>" & Chr(34) & Chr(34) & ",LEFT(.Range(" & Chr(34) & "L" & Chr(34) & " & i),(FIND(" & Chr(34) & "," & Chr(34) & ",.Range(" & Chr(34) & "L" & Chr(34) & " & i))+1))&LEFT(MID(.Range(" & Chr(34) & "L" & Chr(34) & " & i),FIND(" & Chr(34) & "," & Chr(34) & ",.Range(" & Chr(34) & "L" & Chr(34) & " & i))+2,256),2)+1&RIGHT(.Range(" & Chr(34) & "L" & Chr(34) & " & i),4)," & Chr(34) & Chr(34) & ")" ''' Calculate ''' .value = .value ''' But it produces a Run-time error '1004' Application-defined or Object-defined error End If Next i End With 'With [M4:M5] This gives the correct answer but in NEW cells ' [M4].Value = "=IF(L4<>" & Chr(34) & Chr(34) & ",LEFT(L4,(FIND(" & Chr(34) & "," & Chr(34) & ",L4)+1))&LEFT(MID(L4,FIND(" & Chr(34) & "," & Chr(34) & ",L4)+2,256),2)+1&RIGHT(L4,4)," & Chr(34) & Chr(34) & ")" ' [M5].Value = "=IF(L5<>" & Chr(34) & Chr(34) & ",LEFT(L5,(FIND(" & Chr(34) & "," & Chr(34) & ",L5)+1))&LEFT(MID(L5,FIND(" & Chr(34) & "," & Chr(34) & ",L5)+2,256),2)+1&RIGHT(L5,4)," & Chr(34) & Chr(34) & ")" ' Calculate ' .Value = .Value 'End With End Sub Any help would be appreciated.
<tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

 

[Fred_111]

Sorry Fluff, I missed posts #8 & #9
Ok, so I'm learning, Yes the End With position does matter, I'll have to choose my words better, I stand corrected.
Yes my earlier posts didn't mention adding 1 to col F based on another col, it was an after thought, I'll have to explain better.

I'll try your code and get back to you, thanks for your patience.

 

[Fred_111]

Yep, a simple offset.
Works like a charm Fluff.

Thank-you for you time and patience, it's very much appreciated.

 

[Fluff]

Glad to help & thanks for the feedback.