I'm working with a dataset and I have written a loop to check if the cell two below a found instance of a string contains a certain value. If it doesn't I want to concatenate the value of that cell with the cell one below the found cell. And then I want to loop this until the cell two below the found value contains the required string.

I'm sorry if this is confusing, but the code should hopefully speak for itself:

    Dim StrSearch As String
    Dim rng1 As Range
    Dim rng2 As Range
        
    StrSearch = "Ship to"

    With Worksheets(1).UsedRange
        Set rng1 = .Find(StrSearch, , xlValues, xlWhole)
          If Not rng1 Is Nothing Then
             strAddress = rng1.Address
             Set rng2 = rng1
        Do
            Set rng1 = .FindNext(rng1)
            Set rng2 = Union(rng2, rng1)
        Loop While Not rng1 Is Nothing And rng1.Address <> strAddress
    End If
    End With

    If Not rng2 Is Nothing Then
    For Each rng3 In rng2

    Do Until InStr(1, rng3.Offset(2).Value, "End") > 0
    rng3.Offset(1, 1) = "=" & rng3.Offset(1).Value & "&" & ", " & rng3.Offset(2).Value
    rng3.Offset(1, 1).Copy
    rng3.Offset(1).PasteSpecial xlPasteValues
    rng3.Offset(1, 1).Delete Shift:=xlUp
    rng3.Offset(2).Delete Shift:=xlUp
    Loop
    Next
    End If
Everything works fine except for the line

 rng3.Offset(1, 1) = "=" & rng3.Offset(1).Value & "&" & ", " & rng3.Offset(2).Value
which gives a 'run-time error 1004: Application-defined or object-defined error'

So if the data is:

Ship to
UK
USA

I want it to concatenate the line two below 'Ship to' (i.e. USA) with the line one below 'Ship to' (i.e. UK) so the cell reads 'UK, USA'.

So the equivalent of

=A24&", "&A25
for example. But variable depending on the location of the found string.

Again, sorry if this is confusing. Thanks for any help!