使用嵌套的Do While语句运行For循环，但是为什么我的代码冻结？ [英] Running a For loop with a nested Do While statement, but why is my code freezing?

问题描述

我有两本工作簿，一个工作簿中的代码为Sheet1（这是我要从中复制值的工作表的代号），在本示例中定义为 y。我想将Sheet1中单元格 K1-K10的值复制到工作簿y的单元格（ F1-F10）中（例如，我希望F1.Value = K1.Value，F2.Value = K2.Value，等等...）。

I have two workbooks, one with the Code INSIDE of it Sheet1 (which is the codename of the sheet I want to copy values from) and another workbook defined as "y" in this example. I want to copy over the values of cell "K1-K10" in Sheet1 into cell ("F1-F10") of workbook y (For example, I want F1.Value=K1.Value, F2.Value = K2.Value, etc...).

两个工作簿都受密码保护，我只想从Sheet1复制值（这就是为什么我不取消保护它），但是我想保存，一旦将所有值复制并粘贴到工作簿中，就关闭并保护工作簿 y。

Both workbooks are protected with a password, I am only trying to copy values from Sheet1 (that is why I don't unprotect it), but I want to save, close and protect workbook "y" once all of the values copy and paste into it.

当我按下Sheet1中的Activex Commandbutton时，代码冻结了我的工作簿。我也仔细检查了文件路径和工作表名称，它们都是正确的。

When I hit the Activex Commandbutton inside Sheet1, the code is freezing up my workbook. I have double checked the file paths and the sheet names as well, they are correct.

我还发布了代码和以下两个工作簿的屏幕截图：

I have also posted screenshots of the code and both workbooks below:

Private Sub CommandButton1_Click()

Dim y As Workbook
Dim i As Integer

Set y = Workbooks.Open(Filename:="\\FILEPATH\Test 2.xlsm", Password:="Swarf")


    With y

        For i = 1 To 10

            Do While Cells(i, 11).Value <> ""

                .Sheets("MyTest2").Unprotect "Swarf"
                .Sheets("Mytest2").Cells(i, 6).Value = Sheet1.Cells(i, 11).Value

            Loop

        Next i

        .Password = "Swarf"
        .Save
        .Close False

    End With

    End Sub

推荐答案

需要使用循环

Do need use While Loop for this case.

使用如果将解决您的问题。

Private Sub CommandButton1_Click()

Dim y As Workbook
Dim i As Integer

Set y = Workbooks.Open(Filename:="\\FILEPATH\Test 2.xlsm", Password:="Swarf")


    With y

        For i = 1 To 10

            If (Cells(i, 11).Value <> "") Then

                .Sheets("MyTest2").Unprotect "Swarf"
                .Sheets("Mytest2").Cells(i, 6).Value = Sheet1.Cells(i, 11).Value

            End If

        Next i

        .Password = "Swarf"
        .Save
        .Close False

    End With

    End Sub

因为您的 while循环语句将导致无限循环。
例如： i = 1 ，则语句 Cells（i，11）。值<> 始终返回true并继续循环。

Because you will cause infinity loop for your while loop statement. For example: i=1,then the statement Cells(i, 11).Value <> "" always return true and continue looping.

这篇关于使用嵌套的Do While语句运行For循环，但是为什么我的代码冻结？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！