通过VBA复制行时Excel崩溃 [英] Excel Crash When Copying row through VBA

问题描述

Excel VBA上的新手.我只想问一下如何解决我遇到的错误?我正在尝试根据条件复制整个行，然后将其粘贴到另一张纸上(相同的工作簿).这是我的代码:

Newbie here on Excel VBA. I would just like to ask how to fix the error I'm getting? I'm trying to copy an entire row base on a criteria, and paste it on another sheet (same workbook). Here's my code:

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim endRow As Range

Set ws = Sheets("FIELD OFFICE DATABASE")
Set ws1 = Sheets("Transferred Items")

set endRow = Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

ws.Activate
ws.Unprotect "321321"
Range("B2").Select

Do Until endRow
    If ActiveCell.Value = Me.cmbemn.Text Then
        ActiveCell.Rows("B:S").Select
        Selection.copy
        ws1.Activate
        ws1.Unprotect "321321"
        endRow.Select
        ActiveSheet.Paste
    Else
        ActiveCell.Offset(1, 0).Select
    End If
Loop

发生的情况是我的Excel崩溃了，还是弹出了一个错误消息，提示需要对象".但是大多数情况下它崩溃了.

What happens is its either my Excel crashes, or an error pops up saying "Object required". But mostly it crashes.

请帮助！谢谢！

推荐答案

尝试将循环终止条件更改为类似以下内容:

Try changing the loop termination condition to something like:

Do Until ActiveCell.Address = endRow.Address

我认为您可以提前终止:

I think you could terminate earlier with:

Set endRow = ws.Range("B" & Rows.Count).End(xlUp)

您还应该明确说明您正在使用哪个工作表，否则将使用当前的活动表.我假设您正在使用 ws .

You also should be explicit in which worksheet you are working with or else the currently activesheet will be used. I have assumed you are working with ws.

并且 Rows 不应在其中指定列:

And Rows should not specify columns within it:

ActiveCell.Rows("B:S").Select

它应该具有Rows规范.可能您可以执行以下操作:

It should have a Rows specification. Potentially you could do something like:

ActiveCell.EntireRow.Copy

或根据以后的评论

currCell.EntireRow.Copy

不需要.选择，然后选择第二行.您可以一行完成.

There doesn't need to be a .Select then a second line for the copy. You can do in one line.

还请注意@Jeeped对 Unprotect 的评论.

Note also @Jeeped's comment on Unprotect.

我的偏好是指定例如loopRange

My preference would be to specify a loopRange e.g.

Set loopRange = ws.Range("B2:B" & ws.Range("B" & Rows.Count).End(xlUp).Row)

，然后在该范围内循环.

and then loop over that range.

Dim currCell As Range

For Each currCell in loopRange

    If currCell.Value .....do stuff

Next currCell

这具有摆脱使用主动单元的额外好处.

This has the added benefit of getting rid of the use of activecell.

这篇关于通过VBA复制行时Excel崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！