Modify information in a column

  • Hi guys,

    I have this column of information in a worksheet called "Report" in the column "J" I would like to develop a macro to change the writing of the column content for example if is written UNI_EN_ISO_IEC_17025_2005 change it for UNI EN ISO/IEC 17025:2005 and if is written UNI_EN_ISO_IEC_17025_2018 change it for UNI EN ISO/IEC 17025:200518, but I am not able to do it.

    I am struggling because this column "J" does not have always the same leght so I don't know how to difine the range.


    Thank you in advance


    this is the column:

    UNI_EN_ISO_IEC_17025_2018
    UNI_EN_ISO_IEC_17025_2018
    UNI_EN_ISO_IEC_17025_2018
    UNI_EN_ISO_IEC_17025_2018
    UNI_EN_ISO_IEC_17025_2018
    UNI_EN_ISO_IEC_17025_2018
    IO_09_DT
    UNI_EN_ISO_IEC_17025_2018
    UNI_EN_ISO_IEC_17025_2018
    UNI_EN_ISO_IEC_17025_2018
    UNI_EN_ISO_IEC_17025_2018
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
    UNI_EN_ISO_IEC_17025_2005
  • An example workbook would help.


    You could probably just do this without VBA using Find and Replace


    You can define a Range from row 1 to the last used cell in Column J like this


    Code
    1. Sub DefineRange()
    2. Dim rRng As Range
    3. Set rRng = Range(Cells(1, 10), Cells(Rows.Count, 10).End(xlUp))
    4. End Sub