VBA Code To Find Specific Work And Insert Formula Next Cell

[Shums]

Hi All,

I am trying to calculate months remaining between two dates, I am trying with below code, but doesn't do anything...Kindly help....

Sub ChangeMonthsRemaining()
    Dim cl As Range
    Application.ScreenUpdating = False
    For Each cl In Range("$D$2:$D" & Range("$D65536").End(xlUp).Row)
    If UCase(cl) = "Months Remaining" Then
        cl.Offset(0, 1).Formula = "=MONTH($C$1-$D$1)"
    End If
    Next cl
    Application.ScreenUpdating = True
End Sub

 

[VoG]

 If UCase(cl) = "Months Remaining" Then

will never be true. Try something like

 If LCase(cl) = "months remaining" Then

 

[Shums]

Excellent...

Thank you very much.....

 

[Shums]

One more thing, when I am trying

cl.Offset(0, 1).Formula = "=DATEDIF($D$1,$C$1,"M")" it gives me sintax error. End Statement at "M". Any idea????

 

[VoG]

You need to double up the quotes inside the formula. Try

cl.Offset(0, 1).Formula = "=DATEDIF($D$1,$C$1,""M"")"

 

[Shums]

No Luck, it does nothing.....remains the same formula......

 

[Shums]

Actually I wish this to work automated, I dont need to specify cell reference and input current worked month and the months remaining for current year. Do you have any other idea? Please guide me...