Automatically replacing a value in an excel report Solved/Closed

Question

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

TrowaD · Answer

Hi Nazcarr,

Could you show us how your CAB report looks?

Best regards,
Trowa

NazCarr · Answer

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

TrowaD · Answer

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

NazCarr · Answer

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.

Automatically replacing a value in an excel report

4 responses

Similar discussions

Subscribe To Our Newsletter!