时间戳

首页 > 解决方案 > Excel VBA 在单行的两列中查找数据

vba - Excel VBA 在单行的两列中查找数据

问题描述

我试图让这段代码找到 V 列不等于“Y”或“L”且 A 列不为空的行。我想我做得有点过头了，我确信有一种更简单的方法可以检查行上的两个单元格。

Sub EndMove()
Dim Col1 As Integer, Col2 As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String, currentRowValue2 As String

Col1 = 22
Col2 = 1
rowCount = Cells(Rows.Count, Col1).End(xlUp).row

For currentRow = 1 To rowCount
    currentRowValue = Cells(currentRow, Col1).Value
    If currentRowValue <> "y" Or currentRowValue <> "l" Then
    currentRowValue2 = Cells(currentRow, Col2).Value
    If Not IsEmpty(currentRowValue2) Then
    Cells(currentRow, Col1).Select
    MsgBox "Move this?"
End If
End If
Next

结束子

谢谢

标签： vbaexcel

解决方案

你很亲密。我改成currentrow是i因为它更容易多次使用。您还应该限定您的工作表。每当您引用目标工作表上的对象时，请使用ws

这也是区分大小写的。IE Y<> y。如果你想让它忽略大小写，你可以放在Option Compare Text上面Sub EndMove

Option Explicit

Sub EndMove()
Dim rowCount As Long, i As Long

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")

rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

'i refers to row number
For i = 11 To rowCount
    If ws.Range("V" & i) <> "Y" And ws.Range("V" & i) <> "L" Then
        If ws.Range("A" & i) <> "" Then
            'Do what with row i?
        End If
    End If
Next i

End Sub

您也可以像这样将所有 3 个标准组合成一行

For i = 11 To rowCount
    If ws.Range("V" & i) <> "Y" And ws.Range("V" & i) <> "L" And ws.Range("A" & i) <> "" Then
        'Do what with row i?
    End If
Next i

推荐阅读