Loop Without Do, Extracting Year From Date in Exce

Question

So I have this excel spreadsheet that brings in columns from another sheet. For the first four columns, it's just a straight transposition. The fifth in the original sheet - the one that the data is coming from - is a date in dd-mon-yy format (ex. 13-Jul-14), which I need converted to a year (ex. 2014). I get the feeling that this is responsible for the errors I'm getting.

When I run the code, I get the following errors, marked in the code: Loop without Do, End If without Block If, Loop without Do, For without Next.

I'm not very experienced with VBA, especially not in excel, so any advice would be greatly appreciated.

Sub PinkProgram_List()

Dim SiteNoTransfer As String
Dim SiteNo As String

Dim TransferCol(5) As Integer

Dim Row As Integer
Dim RowTransfer As Integer
Dim StartColumn As Integer

TransferCol(0) = 0      'Nothing (placeholder)
TransferCol(1) = 10     'Structure No.
TransferCol(2) = 1      'GWP
TransferCol(3) = 3      'WP
TransferCol(4) = 11     'Work Type
TransferCol(5) = 15     'Completion Year

StartColumn = 45  'Column just left of SiteNo on Master Result sheet
Row = 7          'First row on Master Results sheet


Do
SiteNo = Worksheets("MASTER RESULTS").Cells(Row, StartColumn - 11)
If SiteNo = "" Then
    Exit Do
   ElseIf Not SiteNo = "" Then
    RowTransfer = 4
    Do
        SiteNoTransfer = Worksheets("Program").Cells(RowTransfer, TransferCol(1))
        If SiteNoTransfer = "END" Then
            Exit Do
        ElseIf SiteNoTransfer = SiteNo Then
            Worksheets("MASTER RESULTS").Cells(Row, StartColumn + 1).Interior.Color = RGB(0, 255, 255)
            Worksheets("Program").Cells(RowTransfer, TransferCol(1)).Interior.Color = RGB(0, 100, 255)

            For i = 2 To 4
                If Not TransferCol(i) = 0 Then
                   Worksheets("MASTER RESULTS").Cells(Row, StartColumn + i) = Worksheets("Program").Cells(RowTransfer, TransferCol(i))
                End If
            Next

            For i = 5 To 5
                If Not TransferCol(5) = 0 Then
                     Worksheets("MASTER RESULTS").Cells(Row, StartColumn + i) = Worksheets("Program").Cells(RowTransfer, Year(TransferCol(5))) 'Get the year in yyyy from dd-mon-yy
            Exit Do
        End If
        RowTransfer = RowTransfer + 1
Loop 'Loop without do
End If 'End if without block if

Row = Row + 1
Loop 'Loop without do

End Sub 'For without next

Answer 1

Go ahead and give this re-worked version a try. I have fixed all the syntax issues and cleared the indentation up (so you can more easily see what goes with what), though I'm not sure it's working 100% as you expect it.

Sub PinkProgram_List()

    Dim SiteNoTransfer As String
    Dim SiteNo As String

    Dim TransferCol(5) As Integer

    Dim Row As Integer
    Dim RowTransfer As Integer
    Dim StartColumn As Integer

    TransferCol(0) = 0      'Nothing (placeholder)
    TransferCol(1) = 10     'Structure No.
    TransferCol(2) = 1      'GWP
    TransferCol(3) = 3      'WP
    TransferCol(4) = 11     'Work Type
    TransferCol(5) = 15     'Completion Year

    StartColumn = 45  'Column just left of SiteNo on Master Result sheet
    Row = 7          'First row on Master Results sheet


    Do While True
        SiteNo = Worksheets("MASTER RESULTS").Cells(Row, StartColumn - 11)
        If SiteNo = "" Then
            Exit Do
        ElseIf Not SiteNo = "" Then
            RowTransfer = 4
            Do While True
                SiteNoTransfer = Worksheets("Program").Cells(RowTransfer, TransferCol(1))
                If SiteNoTransfer = "END" Then
                    Exit Do
                ElseIf SiteNoTransfer = SiteNo Then
                    Worksheets("MASTER RESULTS").Cells(Row, StartColumn + 1).Interior.Color = RGB(0, 255, 255)
                    Worksheets("Program").Cells(RowTransfer, TransferCol(1)).Interior.Color = RGB(0, 100, 255)

                    For i = 2 To 4
                        If Not TransferCol(i) = 0 Then
                           Worksheets("MASTER RESULTS").Cells(Row, StartColumn + i) = Worksheets("Program").Cells(RowTransfer, TransferCol(i))
                        End If
                    Next

                    For i = 5 To 5
                        If Not TransferCol(5) = 0 Then
                            Worksheets("MASTER RESULTS").Cells(Row, StartColumn + i) = Worksheets("Program").Cells(RowTransfer, Year(TransferCol(5))) 'Get the year in yyyy from dd-mon-yy
                            Exit Do
                        End If
                    Next
                End If
                RowTransfer = RowTransfer + 1
            Loop
        End If

        Row = Row + 1
    Loop

End Sub

Answer 2

You're missing some endings:

1. If SiteNoTransfer = "END" doesn't have a corresponding End If.

2. For i = 5 To 5 doesn't have a corresponding Next.

Once you fix those, the code will be fine.