VBA Loop for inserting data

[excelproblem101]

Hi all,

I have raw data that I want to convert to be read easier.
There are lines open between each statement.

1. I would need the payment date(M67) to be pasted in every row in column K(In photo - K75 and K76) for the transactions
2. I would need the Internal code(C68) and code(C69) to be pasted next to the payment date (L75,L76 and M75, M76) for all the transactions

Now the tricky part is that there should be a loop to do this over and over for all the statements following from A1 until there is no data left.
Each statement has different amount of transactions inside but the format and position within the sheet remain the same.

 

[Colo]

Hi,
As you say the format and position within the sheet remain the same, so this code would get data using an Offset based on "Bank Name" in column B. Since it's just using a loop, it would be very slow depending on the raw data size. Anyway, give this a try then.

Sub CustomConvert()
    Dim lLastRow As Long    'last data row
    Dim sInternalCode As String
    Dim sCode As String
    Dim sPaymentDate As String
    Dim i As Long, j As Long  'counter for looping

    'Get last row
    lLastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To lLastRow
        With Cells(i, "B")
            If .Value = "Bank Name" Then
                'Getting data
                sInternalCode = .Offset(2, 1).Value
                sCode = .Offset(3, 1).Value
                sPaymentDate = .Offset(1, 11).Value

                'Pasting data
                Do While .Offset(9 + j, -1) <> ""
                    .Offset(9 + j, 9).Value = sPaymentDate
                    .Offset(9 + j, 10).Value = sInternalCode
                    .Offset(9 + j, 11).Value = sCode
                    j = j + 1
                Loop
                j = 0
            End If
        End With
    Next
End Sub

 

[excelproblem101]

Thanks!
Helped alot!