Loop Updates

[Thecraftycarrot]

Hi All,

I am looking to use a loop to update a name within a cell if that name changes and this is the logic behind how i see this working.

E.g. Sheet 1 cell E4 = New name
Sheet 1 cell M45 = Old Name

then if New name <> old name
Find old name in sheet 2 (column b, data starts in row 3) and ends at the last row in table 2

Update for each instance where the old name has been found within the table in column b to the new name (so will need to loop through all data in column b

sorry new to loops and i am struggling to grasp how to program this.

Thanks in advance

 

[Eric W]

I don't think you need a loop. Try:

Sub test2()
Dim MyRange As Range, NewName As String, OldName As String

    NewName = Sheets("Sheet1").Range("E4")
    OldName = Sheets("Sheet1").Range("M45")
    If OldName <> NewName Then
        Set MyRange = Sheets("Sheet5").Range("B3:B" & Sheets("Sheet5").Cells(Rows.Count, "B").End(xlUp).Row)
        MyRange.Replace What:=OldName, Replacement:=NewName, LookAt:=xlWhole
    End If
        
End Sub

 

[Thecraftycarrot]

Hi Eric,

thank you for your help, that did indeed work!

How can i build upon this so that when a name is updated column g is updated - so i have a history of all of the name changes?

thanks again!!

 

[Eric W]

Are you saying that if a name is changed in column B on Sheet5, you want the old name to go to column G on the same row? What if there's already something in G, like if the name was already changed once before?

 

[Thecraftycarrot]

Yes, thats what i wold like. i would like the latest update input at the beginning of the cell, then other information on a new line under this new information within cell

i wouldnt imagine this element will look something like:


xxxxx( code that will move this information into this cell) followed by Chr(10) & old information stored there.

 

[Eric W]

There are some fancy ways to avoid loops in this case too, but I'd move to a loop now for readability. Try:

Sub test2()
Dim MyRange As Range, MyCell As Range, NewName As String, OldName As String

    NewName = Sheets("Sheet1").Range("E4")
    OldName = Sheets("Sheet1").Range("M45")
    If OldName <> NewName Then
        Set MyRange = Sheets("Sheet5").Range("B3:B" & Sheets("Sheet5").Cells(Rows.Count, "B").End(xlUp).Row)
        Set MyCell = MyRange.Find(OldName, LookAt:=xlWhole)
        While Not MyCell Is Nothing
            MyCell.Offset(, 5) = OldName & vbCrLf & MyCell.Offset(, 5)
            MyCell = NewName
            Set MyCell = MyRange.Find(OldName, LookAt:=xlWhole)
        Wend
    End If
        
End Sub

 

[Thecraftycarrot]

Thanks Eric, Much appreciated works like a charm.

 

[Eric W]

You're welcome.