不执行循环，使用VBA在Excel中从日期中提取年份 [英] Loop Without Do, Extracting Year From Date in Excel With VBA

问题描述

所以我有这个excel电子表格，它可以引入另一张表中的列.对于前四列，这只是笔直的换位.原始工作表中的第五个数据(数据来自该数据)是dd-mon-yy格式的日期(例如14年7月13日)，我需要将其转换为年份(例如2014年).我感觉这是我遇到的错误的原因.

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.

运行代码时，出现以下错误，并在代码中进行了标记:不执行循环，结束不执行if结束，不执行循环，不执行Next的情况.

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.

我对VBA的经验不是很丰富，尤其是在excel方面不是很熟练，因此任何建议都将不胜感激.

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

推荐答案

继续尝试此重新设计的版本.我已经解决了所有语法问题，并清除了缩进(以便您可以更轻松地了解所包含的内容)，尽管我不确定它是否按您的预期工作了100％.

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

这篇关于不执行循环，使用VBA在Excel中从日期中提取年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！