Macro to replace part of the text in a cell and move it to another cell.

[samcd87]

I have a macro which takes the text out of col B of the active row and adds some other text and moves it to another column (C)

Often the text in col B will start with "● Alert - 77777777 has done [something]".

"● Alert - " is constant but the number (in this case "77777777") will change depending on the active row. The number is drawn from col D of the active row.

Before the text in col B is moved to col C and more text is added, I'd like to remove ● Alert - 77777777. Is there any way to do this?

The code I currently have is:
Sheets("AddCap").Range("C" & (ActiveCell.Row)).Value = "History of property - removed because" & Sheets("AddCap").Range("B" & (ActiveCell.Row)).Value

But this results in col C saying:
History of property - removed because ● Alert - 77777777 has done something

And I'd like the result in col C to be:
History of property - removed because they have done something

And this is what is contained on each row in the sheet named AddCap:

A	B	C	D
Irrelevant	● Alert - 77777777 has done something		77777777

Any help would be appreciated.

 

[Jimmypop]

Hi there...

What about...

Sheets("AddCap").Range("C" & (ActiveCell.Row)).Value = "History of property - removed because they have done something"

 

[davesexcel]

History of property - removed because they have done something

This doesn't require any cell referencing.

 

[samcd87]

Apologies I haven't been clear here - the 'has done something' element of the text will vary depending on the row, so this is the bit that needs to be pulled over to C.

So it could say:
● Alert - 77777777 has eaten lunch
● Alert - 77777777 has walked the dog
or any number of things - these are just random examples of course.

The result we are then looking for in col C is:
History of property - removed because they have eaten lunch
and
History of property - removed because they have walked the dog

 

[Jimmypop]

How will these examples be entered?

 

[samcd87]

How will these examples be entered?

The sources are entered manually

 

[Jimmypop]

Try the below:

Sub test()
    Dim var As String
    Dim output As String
        var = Range("B" & (ActiveCell.Row)).Value
            output = Right(var, Len(var) - 18)
                Sheets("AddCap").Range("C" & (ActiveCell.Row)).Value = "History of property - removed because they " & output & " "
End Sub

 

[davesexcel]

try this

Sub Do_It()
    Dim sh As Worksheet
    Dim rng As Range, c As Range
    Dim s As String, d As Integer, t As String

    Set sh = ActiveSheet
    s = "History of property - removed because they "
    
    With sh
        Set rng = .Range("B1:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        
        For Each c In rng.Cells
            d = InStr(c, "has") + 3
            If d > 3 Then
                t = Mid(c, d, Len(c))
                t = "have" & t
                c.Offset(, 1) = s & t
            End If
        Next
        
    End With

End Sub