Excel VBA - 循环和 IF 语句结果不是静态的，并且在每次新运行时都会更新

Question

我已经在这个简单的问题上把我的大脑搁置了很长时间，我目前无法弄清楚。情况：我有2列，D和I。D列D填充到行的x＃。我需要在d：x单元格中的每个单元格上搜索一个字符串，并基于if循环为i：x cell

分配一个值

问题：对于每个循环，将存储在单元格I-1通过I-X中的值以最新值进行更新。因此，在第三个循环的末尾，I1-I3中的值都是未知的。任何帮助都将受到赞赏。旧代码

 Sub Country()
    'Variables
    Lastrow = Worksheets("SFDC").UsedRange.Rows.Count
    lastrow2 = Worksheets("SFDC").UsedRange.Rows.Count
    'check the rows for values
    If lastrow2 > 1 Then
        lastrow2 = 0
        Else
    End If
    'Code will run until the last value it reached
    Do While lastrow2 <> Lastrow
        Set Check = Range("D2:D" & Lastrow)
        For Each Cell In Check
            If InStr(Cell, "ANZI-") Then
                Range("I2:I" & cellvalue).Value = "ANZI"
                lastrow2 = lastrow2 + 1
            ElseIf InStr(Cell, "US-") Then
                Range("I2:I" & cellvalue).Value = "US"
                lastrow2 = lastrow2 + 1
            Else
                Range("I2:I" & cellvalue).Value = "Unknown"
                lastrow2 = lastrow2 + 1
            End If
        Next
    Loop
End Sub

新代码，现在这些值正在变化，但仅分配给初始单元格I：2。但是，如果我像以前的编码一样将 1添加到CellValue中，它仍然会覆盖先前的值。

Sub Country()
'Variables
lastrow = Worksheets("SFDC").UsedRange.Rows.Count
'Code will run until the last value it reached
Set Check = Range("D2:D" & lastrow)
cellvalue = 2
For Each Cell In Check
    If InStr(Cell, "ANZI-") Then
        Range("I2:I" & cellvalue).Value = "ANZI"
    ElseIf InStr(Cell, "US-") Then
        Range("I2:I" & cellvalue).Value = "US"
    Else
        Range("I2:I" & cellvalue).Value = "Unknown"
    End If
    cellvalue = cellvalue + 1
Next

结束sub

Answer 1

只需删除Do...Loop，因为For...Next已经为您完成了工作。此外，最好做lastrow2 < Lastrow + 1而不是lastrow2 <> Lastrow

Answer 2

我很愚蠢。

Sub Country()
'Get # of rows on worksheet
lastrow = Worksheets("SFDC").UsedRange.Rows.Count
'Setting variable for the For Loop
Set Check = Range("D2:D" & lastrow)
'will be used as a counter for the cell the return will be placed
cellvalue = 2
'Code will run until through each cell until the last value it reached
For Each Cell In Check
    'if the string is in the D cell then the value will be written to the I cell
    If InStr(Cell, "ANZI-") Then
        Cells(cellvalue, "I") = "ANZI"
    ElseIf InStr(Cell, "US-") Then
        Cells(cellvalue, "I") = "US"
    Else
        Cells(cellvalue, "I") = "Unknown"
    End If
    cellvalue = cellvalue + 1
Next

结束sub

Answer 3

只是注意到您得到了答案，但是由于我已经进行了工作，所以这是您的参考。

Option Explicit
Sub Country()
Dim lastRow As Long
Dim Check As Range
Dim rowNum As Long
Dim cell
'Get # of rows on worksheet
lastRow = Worksheets("SFDC").UsedRange.Rows.Count
'Setting variable for the For Loop
Set Check = Range("D2:D" & lastRow)
'will be used as a counter for the cell the return will be placed
'Code will run until through each cell until the last value it reached
For Each cell In Check
    rowNum = cell.Row()
    'if the string is in the D cell then the value will be written to the I cell
    If InStr(cell, "ANZI") Then
        Range("I" & rowNum) = "ANZI"
    ElseIf InStr(cell, "US") Then
        Range("I" & rowNum) = "US"
    Else
        Range("I" & rowNum) = "Unknown"
    End If
Next
End Sub