try
Option Explicit
Sub ISIN_Copy()
On Error GoTo ErrorHandler
Dim xlRng As Range
Dim cellVal
Dim fAddr$
Dim i&, lngRowStart&
lngRowStart = 2
For Each cellVal In Range(Cells(2, 4), Cells(Cells(2, 4).End(xlDown).Row, 4)).Value
Set xlRng = Columns(2).Find(What:="1", LookIn:=xlValues, lookat:=xlWhole, After:=Cells(lngRowStart, 2))
If Not xlRng Is Nothing Then
If fAddr = vbNullString Then
fAddr = xlRng.Address
ElseIf fAddr = xlRng.Address Then
Exit For
End If
Range(Cells(lngRowStart, 3), Cells(xlRng.Row - 1, 3)).Value = cellVal
lngRowStart = xlRng.Row
Else
Exit For
End If
Next cellVal
Exit Sub
ErrorHandler:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "Error"
End Sub
Bookmarks