Automatically replacing a value in an excel report [Solved]

Report
Posts
13
Registration date
Monday March 25, 2019
Status
Member
Last seen
January 26, 2021
-
Posts
13
Registration date
Monday March 25, 2019
Status
Member
Last seen
January 26, 2021
-
Hello,
I have a CAB Report template with a column called "Change Type"
When I extract information the data in this column comes in as either Normal or Standard

I would like Excel to automatically convert "Standard" to "Request for a New Standard Change" every time the report is populated.

Could someone help me out with this, please? I was thinking of adding a macro to the report template but not sure what code to put in.

Many Thanks



System Configuration: Windows / Chrome 81.0.4044.129

4 replies

Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
457
Hi Nazcarr,

I guess this one slipped my attention.

You could just use the Find&Replace option (Ctrl+H) and make sure to mark the option for identical cell value.

Or use the code below:
Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Range("E2:E" & Range("E1").End(xlDown).Row)
    If cell.Value = "Standard" Then cell.Value = "Request for a New Standard Change"
Next cell
End Sub


Best regards,
Trowa
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
457
Hi Nazcarr,

Could you show us how your CAB report looks?

Best regards,
Trowa
Posts
13
Registration date
Monday March 25, 2019
Status
Member
Last seen
January 26, 2021

Hi Trowa,
My sincere apologies for the lack of response on my part! I got pulled off this piece of work and had to focus on something else - Covid has us all on our toes! I have inserted an image of what the excel spreadsheet looks like. The highlighted bit comes in as "Standard" and I am having to manually retype this as "Request for a New Standard Change"



Many Thanks,
Naz
Posts
13
Registration date
Monday March 25, 2019
Status
Member
Last seen
January 26, 2021

Thanks Trowa! My apologies for the late reply - we have been debating on whether we can use a different reporting tool rather than Excel. But the above has worked! Many thanks once again.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!