Macro to Insert text in a cell based on a condition.

[kshitij_dch]

Hi all,

I have a excel sheet , In J column i have 'Policy Type' , In AK Column I have 'Final Status'.

I am looking for a macro code that can search 'Renew' in Column J (Policy Type) , Against all Renew in Column AK , it should insert 'To be Defined'.

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Policy Type (J Column)[/TD]
[TD]Final Status (AK Column)[/TD]
[/TR]
[TR]
[TD]New [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Renew[/TD]
[TD]To be Defined[/TD]
[/TR]
</tbody>[/TABLE]
anticipate a response :D

 

[mikerickson]

=IF(J1="Renew", "To Be Defined", "") should do what you want.

 

[kshitij_dch]

Thank you for a quick reply Mikerickson , actually this is a macro enabled file and i have few codes already running in the file.

I am looking to add a code in same module , otherwise macro code will overwrite the formula ends in no result.

 

[My Aswer Is This]

If column "J" has "Renew" you want "To Be Defined in column "AK"

Try this:

Sub Renew_Yes()
Application.ScreenUpdating = False
Dim c As Range
    For Each c In Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
        If c.Value = "Renew" Then c.Offset(, 27).Value = "To be Defined"
    Next
Application.ScreenUpdating = True
End Sub

 

[kshitij_dch]

Thanks it is working , is it possible that In column J if it is 'renew' then in column AK cell should be blank then only it should insert "To be defined"

 

[My Aswer Is This]

Try this:

Sub Renew_Yes()
Application.ScreenUpdating = False
Dim c As Range
    For Each c In Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
        If c.Value = "Renew" Then If c.Offset(, 27).Value = "" Then c.Offset(, 27).Value = "To be Defined"
    Next
Application.ScreenUpdating = True
End Sub

 

[kshitij_dch]

Thank you Sir , its indeed a great help

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thank you Sir , its indeed a great help