Hello, I would like to write a macro to return values in adjacent cell if a cell contains certain text. For example, if D2 contains any of the following, write the corresponding value in E2; same for D3, D4, etc.:
If D2 contains "REG_EU", write Europe in E2 and/or
If D2 contains "CN", write China in E2 and/or
If D2 contains "US", write North America in E2 and/or
If D2 contains "REG_WORLD", write North America, Europe and China in E2.
I've included a sample for reference.
Any help would be appreciated. Thanks!
[TABLE="width: 788"]
<tbody>[TR]
[TD="width: 120, bgcolor: #BC4341"]SUBID
[/TD]
[TD="width: 120, bgcolor: #BC4341"]PFID
[/TD]
[TD="width: 200, bgcolor: #BC4341"]Short Description
[/TD]
[TD="width: 381, bgcolor: #BC4341"]Long Description
[/TD]
[TD="width: 231, bgcolor: #BC4341"]Impacted Region
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]200000002401
[/TD]
[TD="width: 120, bgcolor: transparent"]00004346
[/TD]
[TD="width: 200, bgcolor: transparent"]Significant Change Specification: 200000002401 with PFID 00004346
[/TD]
[TD="width: 381, bgcolor: transparent"]Significant change: Complete GHS revisions for specification 200000002401. ||| Substance ID #: 200000002401 CN - Significant change due to HAZARDOUS INGREDIENTS ||| |||Note significant change reason. Run corresponding " RULES" to completion, create reports with languages from generation variants and extract GHS label data, when applicable,for products that require a GHS label.
[/TD]
[TD="width: 231, bgcolor: transparent"]China
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]200000002402
[/TD]
[TD="width: 120, bgcolor: transparent"]00004397
[/TD]
[TD="width: 200, bgcolor: transparent"]Significant Change Specification: 200000002402 with PFID 00004397
[/TD]
[TD="width: 381, bgcolor: transparent"]Significant change: Complete GHS revisions for specification 200000002402. ||| Substance ID #: 200000002402 ||| US - Significant change due to HAZARDOUS INGREDIENTS ||| REG_EU - Significant change due to HAZARDOUS INGREDIENTS ||| CN - Significant change due to HAZARDOUS INGREDIENTS ||| |||Note significant change reason. Run corresponding " RULES" to completion, create reports with languages from generation variants and extract GHS label data, when applicable,for products that require a GHS label.
[/TD]
[TD="width: 231, bgcolor: transparent"]North America, Europe, China
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]200000002411
[/TD]
[TD="width: 120, bgcolor: transparent"]00004850
[/TD]
[TD="width: 200, bgcolor: transparent"]Significant Change Specification: 200000002411 with PFID 00004850
[/TD]
[TD="width: 381, bgcolor: transparent"]Significant change: Complete GHS revisions for specification 200000002411. ||| Substance ID #: 200000002411 ||| REG_EU - Significant change due to HAZARDOUS INGREDIENTS ||| CN - Significant change due to HAZARDOUS INGREDIENTS ||| |||Note significant change reason. Run corresponding " RULES" to completion, create reports with languages from generation variants and extract GHS label data, when applicable,for products that require a GHS label.
[/TD]
[TD="width: 231, bgcolor: transparent"]Europe, China
[/TD]
[/TR]
</tbody>[/TABLE]
If D2 contains "REG_EU", write Europe in E2 and/or
If D2 contains "CN", write China in E2 and/or
If D2 contains "US", write North America in E2 and/or
If D2 contains "REG_WORLD", write North America, Europe and China in E2.
I've included a sample for reference.
Any help would be appreciated. Thanks!
[TABLE="width: 788"]
<tbody>[TR]
[TD="width: 120, bgcolor: #BC4341"]SUBID
[/TD]
[TD="width: 120, bgcolor: #BC4341"]PFID
[/TD]
[TD="width: 200, bgcolor: #BC4341"]Short Description
[/TD]
[TD="width: 381, bgcolor: #BC4341"]Long Description
[/TD]
[TD="width: 231, bgcolor: #BC4341"]Impacted Region
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]200000002401
[/TD]
[TD="width: 120, bgcolor: transparent"]00004346
[/TD]
[TD="width: 200, bgcolor: transparent"]Significant Change Specification: 200000002401 with PFID 00004346
[/TD]
[TD="width: 381, bgcolor: transparent"]Significant change: Complete GHS revisions for specification 200000002401. ||| Substance ID #: 200000002401 CN - Significant change due to HAZARDOUS INGREDIENTS ||| |||Note significant change reason. Run corresponding " RULES" to completion, create reports with languages from generation variants and extract GHS label data, when applicable,for products that require a GHS label.
[/TD]
[TD="width: 231, bgcolor: transparent"]China
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]200000002402
[/TD]
[TD="width: 120, bgcolor: transparent"]00004397
[/TD]
[TD="width: 200, bgcolor: transparent"]Significant Change Specification: 200000002402 with PFID 00004397
[/TD]
[TD="width: 381, bgcolor: transparent"]Significant change: Complete GHS revisions for specification 200000002402. ||| Substance ID #: 200000002402 ||| US - Significant change due to HAZARDOUS INGREDIENTS ||| REG_EU - Significant change due to HAZARDOUS INGREDIENTS ||| CN - Significant change due to HAZARDOUS INGREDIENTS ||| |||Note significant change reason. Run corresponding " RULES" to completion, create reports with languages from generation variants and extract GHS label data, when applicable,for products that require a GHS label.
[/TD]
[TD="width: 231, bgcolor: transparent"]North America, Europe, China
[/TD]
[/TR]
[TR]
[TD="width: 120, bgcolor: transparent"]200000002411
[/TD]
[TD="width: 120, bgcolor: transparent"]00004850
[/TD]
[TD="width: 200, bgcolor: transparent"]Significant Change Specification: 200000002411 with PFID 00004850
[/TD]
[TD="width: 381, bgcolor: transparent"]Significant change: Complete GHS revisions for specification 200000002411. ||| Substance ID #: 200000002411 ||| REG_EU - Significant change due to HAZARDOUS INGREDIENTS ||| CN - Significant change due to HAZARDOUS INGREDIENTS ||| |||Note significant change reason. Run corresponding " RULES" to completion, create reports with languages from generation variants and extract GHS label data, when applicable,for products that require a GHS label.
[/TD]
[TD="width: 231, bgcolor: transparent"]Europe, China
[/TD]
[/TR]
</tbody>[/TABLE]