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
for example. But variable depending on the location of the found string.
Again, sorry if this is confusing. Thanks for any help!
Bookmarks