我的代码可以工作，但Excel停止响应。代码中没有错误的mgs

Question

我需要一些帮助来使我的conde更简单。我开始在VBA上编写代码，并构建自己的脚本，有时它们可以正常工作。但它们总是太大了，而且比实际情况复杂得多。

这是一种每次我运行脚本时，Excel崩溃的情况。有没有人能帮我把这段代码写得更简单？

    Sub Cleaning_Mirexs()

    Application.ScreenUpdating = False

    Dim UltCel As Range
    Dim Mirex As String
    Dim Glip As String

Mirex = "S"
Glip = "UP"

Set UltCel = Cells(Rows.Count, 2).End(xlUp)

' Moving Data for treatment

    Range("R2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("X2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("X2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

' Mirex Formicide Data

Range("$Y2").Select

    Do While ActiveCell <> UltCel
    If InStr(1, ActiveCell.Text, Mirex) Then
    ActiveCell.FormulaR1C1 = ""
    ActiveCell.Offset(0, -1).Select
    ActiveCell.Clear
    ActiveCell.FormulaR1C1 = "IS FORMICIDA MIREX-S" & ActiveCell.Value
    ActiveCell.Offset(1, 1).Select

    ElseIf ActiveCell.Offset(xlDown) Then

    End If

    Loop

' Glip Herbicide Data

Range("Y2").Select

    Do While ActiveCell <> UltCel
    If InStr(1, ActiveCell.Text, Glip) Then
    ActiveCell.Formula = ""
    ActiveCell.Offset(0, -1).Select
    ActiveCell.Clear
    ActiveCell.FormulaR1C1 = "HB GLIP-UP" & ActiveCell.Value
    ActiveCell.Offset(1, 1).Select

    ElseIf ActiveCell.Offset(1, 0).Select Then

    End If

    Loop

' Light Tractor Data
Range("X2").Select

    Do While ActiveCell <> UltCel
    If InStr(1, ActiveCell.Text, "Tratores leves") Then
    ActiveCell.Clear
    ActiveCell.FormulaR1C1 = "Tratores leves" & ActiveCell.Value
    ActiveCell.Offset(1, 0).Select

    ElseIf ActiveCell.Offset(1, 0).Select Then

    End If

    Loop

' Heavy Tractor Data
Range("X2").Select

    Do While ActiveCell <> UltCel
    If InStr(1, ActiveCell.Text, "Tratores pesados") Then
    ActiveCell.Clear
    ActiveCell.FormulaR1C1 = "Tratores pesados" & ActiveCell.Value
    ActiveCell.Offset(1, 0).Select

    ElseIf ActiveCell.Offset(1, 0).Select Then

    End If

    Loop

' back to original place after data treatment
Range("X2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("X2").Select
    Selection.PasteSpecial Paste:=xlPasteValues


Application.ScreenUpdating = True

MsgBox "Success!"

End Sub

我希望代码可以一次运行所有内容，但他们按照我编写脚本的方式，感觉像是为每个数据集单独运行一次。

好了，这就是了！让我们尽情享受吧:)

谢谢!

玛丽亚

​

​

Answer 1

好吧，我试着解决这个问题，但我有几个问题关于你想在这里实现的目标……例如：

ActiveCell.Clear
ActiveCell.FormulaR1C1 = "Tratores pesados" & ActiveCell.Value

在这里，您只需清除您的ActiveCell，然后添加一些文本，然后添加ActiveCell.Value，因为您刚刚清除了它。我不知道你的意图是什么。

您还可以

ElseIf ActiveCell.Offset(1, 0).Select Then
End If

我认为它没有任何功能，我很困惑，只是试图理解为什么这是必要的，所以我省略了它。

我还去掉了你的Do/Loop，取而代之的是更可靠的For循环。我也在很大程度上摆脱了Select/Activate，因为它们的效率很低。

我还将UltCel更改为For循环的Long。

如果其他人想要编辑这篇文章，请继续，我相信我遗漏了一些东西(比如我对.TextToColumns部分不太确定：

Sub Cleaning_Mirexs()

Application.ScreenUpdating = False

Dim UltCel As Long
Dim Mirex As String, Glip As String
Dim i As Long

Mirex = "S"
Glip = "UP"

UltCel = Cells(Rows.Count, 2).End(xlUp)

'Moving Data for treatment
Range("X2:X" & UltCel).Value = Range("R2:R" & UltCel).Value
Range("X2:X" & UltCel).TextToColumns Destination:=Range("X2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

For i = 2 To UltCel
    If InStr(Range("X" & i).Value, Mirex) Then
        Range("X" & i).Value1 = "IS FORMICIDA MIREX-S"
    ElseIf InStr(Range("X" & i).Value, Glip) Then
        Range("X" & i).Value = "HB GLIP-UP"
    ElseIf InStr(Range("X" & i).Value, "Tratores leves") Then
        Range("X" & i).Value = "Tratores leves"
    ElseIf InStr(Range("X" & i).Value, "Tratores pesados") Then
        Range("X" & i).Value = "Tratores pesados"
    End If
Next i

For i = 2 To UltCel
    If InStr(Range("Y" & i).Value, Mirex) Then
        Range("Y" & i).Value1 = "IS FORMICIDA MIREX-S"
    ElseIf InStr(Range("Y" & i).Value, Glip) Then
        Range("Y" & i).Value = "HB GLIP-UP"
    ElseIf InStr(Range("Y" & i).Value, "Tratores leves") Then
        Range("Y" & i).Value = "Tratores leves"
    ElseIf InStr(Range("Y" & i).Value, "Tratores pesados") Then
        Range("Y" & i).Value = "Tratores pesados"
    End If
Next i

'back to original place after data treatment
Range("X2:X" & UltCel).Value = Range("X2:X" & UltCel).Value
Range("Y2:Y" & UltCel).Value = Range("Y2:Y" & UltCel).Value

Application.ScreenUpdating = True

MsgBox "Success!"

End Sub