How to change or replace multiple cell name in Microsoft Excel chronologically?

[tonmoy6794]

I have provided the screenshot of my excel worksheet. I want to rename the cells in column B chronologically same as Column A but without losing the blank cells. i.e the first "transfer" name cell of column B will be "transfer 1", the second "transfer" name cell of column B will be "transfer 2", the third one will be "transfer 3" and so on. Is that possible? If so how and is there a easy method to do that? Also if that is not possible, is there a way to arrange the cell of Column A same as Column B. i.e arranging the cells of column A exactly like column B and have those blank cells in between.

I am complete noob at excel so please be detailed with your answers if possible. Thank you.

I have tried several formulas and methods but none of them gave me the desired result that I expected. I have not tried VBA though because I dont know how to use it

 

[jkpieterse]

Cross-posted: How to change or replace multiple cell name in Microsoft Excel chronologically?

 

[mumps]

Try:

Sub ReplaceName()
    Application.ScreenUpdating = False
    Dim sAddr As String, fnd As Range, x As Long: x = 2
    Set fnd = Range("B:B").Find("Transfer1", LookIn:=xlValues, LookAt:=xlWhole)
    If Not fnd Is Nothing Then
        sAddr = fnd.Address
        Do
            fnd = Range("A" & x)
            x = x + 1
            Set fnd = Range("B:B").FindNext(fnd)
        Loop While fnd.Address <> sAddr
        sAddr = ""
    End If
    Application.ScreenUpdating = True
End Sub