运行和的条件循环跳过值。

Question

我创建了这个代码，目的是：

第一个循环：

• 循环遍历column O中的单元格，查找以DAAP开头的任何内容。
• 如果DAAP左侧的单元格5列为空，则将单元格1列添加到Total的右边(这是一个数字)。然后将单元重命名为GAAF
• 如果单元格5列不是空的，那么只需将单元格重命名为GAAF

第二循环：

• 在GAAF中查找column O中的所有单元格，然后将单元格1列更改为Total

下面是我的代码，它运行时没有错误，但什么也不做。我假设某些东西语法错误，所以它找错地方了，但我找不到它！任何帮助都是非常感谢的:)

Dim rng As Range
Dim lstRow As Long
Dim AUMCell As Range
Dim Total As Long

lstRow = Cells(Rows.Count, "O").End(xlUp).Row

Set rng = Range("O2", Cells(lstRow, "O"))

Total = 0

For Each AUMCell In rng
    If AUMCell.value = "DAAP" & "*" And AUMCell.Offset(0, -5).value = "" Then
        Total = Total + AUMCell.Offset(0, 1).Value
        AUMCell.value = "GAAF"
    ElseIf AUMCell.value = "DAAP" & "*" And AUMCell.Offset(0, -5).value <> "" Then
        AUMCell.value = "GAAF"
    End If
Next AUMCell

For Each AUMCell In rng
    If AUMCell.Value = "GAAF" Then
        AUMCell.Offset(0, 1).Value = Total
    End If
Next AUMCell

Answer 1

虽然代码更长，但Find的执行要比通过每个单元格的循环快得多。

    Sub Recut()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range

    Dim aCell As Range
    Dim bCell As Range
    Dim ws As Worksheet
    Dim SearchString As String


    Set ws = ActiveSheet
    Set rng1 = ws.Range("O:O")

    SearchString = "DAAP"

    Set aCell = rng1.Find(SearchString, , xlFormulas, xlPart, xlByRows, xlNext)

    If Not aCell Is Nothing Then
        Set bCell = aCell
        If Left$(aCell.Value, 4) = SearchString Then Set rng2 = aCell
        Do
            Set aCell = rng1.FindNext(After:=aCell)
             If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                 If Left$(aCell.Value, 4) = SearchString Then
                    If Not rng2 Is Nothing Then
                        Set rng2 = Union(rng2, aCell)
                    Else
                        Set rng2 = aCell
                    End If
                 End If
            Else
                Exit Do
            End If
        Loop
    Else
        MsgBox SearchString & " not Found"
        Exit Sub
    End If

    If rng2 Is Nothing Then Exit Sub

    For Each rng3 In rng2
      If Len(rng3.Offset(0, -5)) = 0 Then Total = Total + rng3.Offset(0, 1).Value
      rng3.Value = "GAFF"
    Next

    MsgBox Total

End Sub

Answer 2

尝试替换此代码

For Each AUMCell In rng
    If AUMCell.value = "DAAP" & "*" And AUMCell.Offset(0, -5).value = "" Then
        Total = Total + AUMCell.Offset(0, 1).Value
        AUMCell.value = "GAAF"
    ElseIf AUMCell.value = "DAAP" & "*" And AUMCell.Offset(0, -5).value <> "" Then
        AUMCell.value = "GAAF"
    End If 
Next AUMCell

按此代码

For Each AUMCell In rng
    If AUMCell.value like "DAAP*" Then 
        If AUMCell.Offset(0, -5).value = "" Then
            Total = Total + AUMCell.Offset(0, 1).Value
        End If        
        AUMCell.value = "GAAF"
    End If
Next AUMCell

对不起，我的英语