我在VBA中的while循环中有一个特定的行，它不执行但每个其他行都执行

Thank you in advance for going through this question. I am a newbie in Excel VBA world.

提前感谢您完成此问题。我是Excel VBA世界的新手。

I have a spreadsheet which I am working on. This spreadsheet had some raw data with multiple columns and rows. I am trying to loop through the whole sheet and based on certain conditions (like the date falls in this week or item has a value Open), I am trying to copy the certain cells of a row to the other tab (sheet) of the spreadsheet.

我有一个我正在处理的电子表格。此电子表格包含一些包含多个列和行的原始数据。我试图循环整个工作表并基于某些条件（如本周的日期或项目有一个值打开），我试图将一行的某些单元格复制到另一个选项卡（工作表）电子表格。

So now, my while loop works okay and some cell values are getting copied to the next tab but just one cell value is not getting copied. When I try to debug it. I see the line is getting executed but the value is not getting copied. I double checked to see if I am referring to the correct column and it is correct. Please see the code below. I have Sht1 and Sht2 as public constants defined outside of Sub which is for sheet names "Tracking Sheet" and "ItemsDueSheet".

所以现在，我的while循环工作正常，一些单元格值被复制到下一个选项卡，但只有一个单元格值没有被复制。当我尝试调试它。我看到该行正在执行但该值未被复制。我仔细检查，看看我是否指的是正确的列，这是正确的。请参阅下面的代码。我有Sht1和Sht2作为在Sub之外定义的公共常量，用于表单名称“Tracking Sheet”和“ItemsDueSheet”。

Public Const Sht1 = "Tracking Sheet"
Public Const Sht2 = "ItemsDueSheet"

Sub ItemsDueThisWeek()

Dim rwCounter As Integer
Dim rstart As Integer
Dim rEnd As Integer
Dim increment As Integer
rstart = 3
rEnd = 249
rwCounter = 5
increment = 1
While (rstart <= rEnd)
    'MsgBox (Format(Sheets(1).Cells(rStart, 16)))
    If (Format(Sheets(Sht1).Cells(rstart, 16), "ww-yyyy") = Format(Date, "ww-yyyy") And Sheets(Sht1).Cells(rstart, 15) <> "Closed - Done") Then
        Sheets(Sht2).Cells(rwCounter, 2) = increment
        Sheets(Sht2).Cells(rwCounter, 4) = Sheets(Sht1).Cells(rstart, 1)
        Sheets(Sht2).Cells(rwCounter, 5) = Sheets(Sht1).Cells(rstart, 2)
        **Sheets(Sht2).Cells(rwCounter, 8) = Sheets(Sht1).Cells(rstart, 7)**
        Sheets(Sht2).Cells(rwCounter, 3) = Sheets(Sht1).Cells(rstart, 13)
        Sheets(Sht2).Cells(rwCounter, 6) = Sheets(Sht1).Cells(rstart, 16)
        Sheets(Sht2).Cells(rwCounter, 7) = Sheets(Sht1).Cells(rstart, 12)

        rwCounter = rwCounter + 1
        increment = increment + 1
        'MsgBox ("A")

    Else

        'Sheets(2).Cells(rwcounter, 5) = Sheets(1).Cells(rStart, 1)
        'Sheets(2).Cells(rwcounter, 6) = Sheets(1).Cells(rStart, 2)
    End If

    rstart = rstart + 1

Wend

End Sub

Here is the layout of ItemsdueSheet where I am trying to copy the cell based on button click from tracking sheet.

这是ItemsdueSheet的布局，我试图根据跟踪表中的按钮单击来复制单元格。

1 个解决方案

#1

@KacireeSoftware - So I am testing if the current date falls in this week and the column O Child status is not equal to Closed-Done then copy CAPA#, Child Task Number, Short Description, Due Date, Workstream member and Entity to the new sheet. Your assumption is right. I have highlighted the code which is not working. Currently, how the spreadsheet is set up, wherever there is a product, there is no Child Due date and where there is child task there is no due date.

@KacireeSoftware - 所以我正在测试当前日期是否在本周，并且O Child状态列不等于Closed-Done，然后将CAPA＃，子任务编号，简短描述，截止日期，工作流成员和实体复制到新的片。你的假设是对的。我突出显示了无效的代码。目前，如何设置电子表格，只要有产品，没有儿童截止日期，并且有儿童任务的地方没有截止日期。

@YowE3K - Yes Column G is only populated for header record for each CAPA#.

@YowE3K - 是列G仅填充每个CAPA＃的标题记录。

Thank you @KacireeSoftware and @YowE3K for your help. Your questions made me help find the problem.

感谢@KacireeSoftware和@ YowE3K的帮助。您的问题让我帮助您找到问题所在。

The problem with the code was that it is currently testing if current date falls in this week and the column O Child status is not equal to Closed-Done then copy CAPA#, Child Task Number, Short Description, Due Date, Workstream member and Entity to the new sheet. However, the Product/Process Entity column is empty in the spreadsheet for those rows. So even though it executes it, it finds nothing to copy there and copies blank in those rows.

该代码的问题在于它当前正在测试当前日期是否在本周，并且O Child状态列不等于Closed-Done然后复制CAPA＃，子任务编号，简短描述，截止日期，工作流成员和实体到新表。但是，这些行的电子表格中的“产品/流程实体”列为空。因此即使它执行它，它也没有找到任何东西来复制它们并在那些行中复制空白。

Thank you once again making me think in that direction. You guys are awesome.

再次感谢你让我思考这个方向。你们真棒。

#1