Macro to create a new line within a text string if specific characters appear mid-string

[Groovicles]

hi everyone,

I have the following code:

Sub PointForm()
    Dim intcount As Integer
    Dim rngRange As Range
    Dim strTMP As String
    Dim splitVals As Variant
    Dim totalVals As Long
    For Each rngRange In Range("$E$1:$E$3000, $G$1:$G$3000")
    On Error Resume Next
        If Trim(rngRange.Value) <> "" Then
            strTMP = rngRange.Text
            intcount = InStr(strTMP, "&&")
            If intcount <> 0 Then
                splitVals = Split(ActiveCell.Value, Chr(10))
                totalVals = UBound(splitVals)
            End If
        End If
    On Error GoTo 0
    Next rngRange
End Sub

What this code is supposed to do, is go through each cells in the ranges specified. If the text strings in the cell contains a && the a new line after the && is created and bringing the remaining string to the right of the && with it. I do not get an error with this only, only it doesn't actually seem to do anything.

So here's the effect:

Current (in a single cell):
The sun is out && The sun is out

Desired result (in a single cell):

The sun is out
The sun is out

Any ideas?

Thanks everyone!

[Olly]

Try simply:

Sub foo()
Sheet1.Range("$E$1:$E$3000, $G$1:$G$3000").Replace What:="&&", Replacement:=Chr(10)
End Sub

[Groovicles]

Olly,

You, good sir, are a genius. Thanks very much

[Olly]

Happy to help. Thanks for the feedback.