If cell contains certain text then replace with another cell

[MechEng_Yoshi]

For example if I have range from G1:G1000 and with some ones name and another with their name and graduation year (EX: John Smith '19). How could I use vba to recognize the fields that don't have graduation year and replace with the cell that has the correct text. I have thousands of names and grad years from '60 - '20. Thank you

 

[Saurabhj]

Hi, one query - if you find name with graduation year would you like to replace it with name only. Ex: John Smith '19 will be converted to John Smith.

 

[MechEng_Yoshi]

If I find name with graduation year I would like to replace name with name+graduation year. EX John smith converts to John Smith ‘19

 

[Saurabhj]

Sorry, didn't understand the requirement.

IF cell contains John Smith ‘19, it would remain as is.
IF cell contains Matt Nelson, it should change to Matt Nelson '19.

Is my understanding correct ?

 

[MechEng_Yoshi]

Correct.

 

[Saurabhj]

Hi,

Please check below code. It will replace the text as per the requirement.

Sub replace()
    Dim lastRow As Integer, rowno As Integer
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    For rowno = 2 To lastRow
        If InStr(1, ActiveSheet.Cells(rowno, 1), "'") = 0 Then
            ActiveSheet.Cells(rowno, 1) = ActiveSheet.Cells(rowno, 1) & " '19"
        End If
    Next

End Sub

 

[MechEng_Yoshi]

I think you misunderstood me. There are many graduation years ranging from '60 - '20 and some people don't even have a graduation year associated with their name. Sorry for the misunderstanding

 

[Saurabhj]

Is the graduation year in any column ?

Can you please share your data using XL2BB. It would help to copy the data and understand the requirement.