Automatically replacing a value in an excel report

Solved/Closed
NazCarr Posts 13 Registration date Monday March 25, 2019 Status Member Last seen January 26, 2021 - Aug 6, 2020 at 05:49 AM
NazCarr Posts 13 Registration date Monday March 25, 2019 Status Member Last seen January 26, 2021 - Dec 14, 2020 at 09:51 AM
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 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 15, 2020 at 12:01 PM
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 20, 2020 at 11:30 AM
Hi Nazcarr,

Could you show us how your CAB report looks?

Best regards,
Trowa
NazCarr Posts 13 Registration date Monday March 25, 2019 Status Member Last seen January 26, 2021
Oct 7, 2020 at 06:17 AM
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
NazCarr Posts 13 Registration date Monday March 25, 2019 Status Member Last seen January 26, 2021
Dec 14, 2020 at 09:51 AM
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.