Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Automatically replacing a value in an excel report

Report
Posts
11
Registration date
Monday March 25, 2019
Status
Member
Last seen
October 7, 2020
 -
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
 -
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
See more

3 replies

Answer 1 / 3
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
 446
Hi Nazcarr,

Could you show us how your CAB report looks?

Best regards,
Trowa
Answer 2 / 3
Posts
11
Registration date
Monday March 25, 2019
Status
Member
Last seen
October 7, 2020
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
Answer 3 / 3
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
 446
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
Reply Ask a question