vba 如果 IsEmpty 然后转到 A 列但在同一行并提取该数据

If IsEmpty then go to column A but in same row and extract that data

提问by user2664400

I am creating a spreadsheet which has many columns and I am trying to make it check if a cell in a certain column is empty then it will check to see the cells in the other columns of the same row are empty. Currently the only way I know of is if you say exactly what cell you are checking.

我正在创建一个有很多列的电子表格，我试图让它检查某个列中的单元格是否为空，然后它会检查同一行的其他列中的单元格是否为空。目前我所知道的唯一方法是，如果您准确说出您正在检查的单元格。

'Checking If the "Email Sent Columb cells are empty so it can be set to "No"
If IsEmpty(ThisWorkbook.Sheets(2).Range("F2")) Then
ThisWorkbook.Sheets(2).Range("F2") = "No"
  If IsEmpty(ThisWorkbook.Sheets(2).Range("J2")) Then
  ThisWorkbook.Sheets(2).Range("J2") = "No"
    If IsEmpty(ThisWorkbook.Sheets(2).Range("M2")) Then
    ThisWorkbook.Sheets(2).Range("M2") = "No"
    End If
End If
End If

'If Acknowledge Email Sent = No then it will check if there are any fields that are       empty if not then it will send the email and mark
'it as sent
If ThisWorkbook.Sheets(2).Range("F2") = "No" Then

If IsEmpty(ThisWorkbook.Sheets(2).Range("A2")) Then
MsgBox "A2 Empty"
Else
If IsEmpty(ThisWorkbook.Sheets(2).Range("B2")) Then
MsgBox "B2 Empty"
Else
    If IsEmpty(ThisWorkbook.Sheets(2).Range("C2")) Then
    MsgBox "C2 Empty"
    Else
        If IsEmpty(ThisWorkbook.Sheets(2).Range("D2")) Then
        MsgBox "D2 Empty"
        Else
            If IsEmpty(ThisWorkbook.Sheets(2).Range("E2")) Then
            MsgBox "E2 Empty"
            Else
            MsgBox "Acknowledge Email Ready To Be Sent"
            ThisWorkbook.Sheets(2).Range("F2") = "Yes"
            End If
        End If
    End If
End If
End If

End If

'If Update Email Sent = No then it will check if further evidence is required if it is     then it will check if the evidence required is
'not empty, if it isnt empty then it will send the email and mark it as sent, but if further evidence required is set to no then it
'will mark it as unnecessary.
If ThisWorkbook.Sheets(2).Range("J2") = "No" Then

If ThisWorkbook.Sheets(2).Range("H2") = "Yes" Or ThisWorkbook.Sheets(2).Range("H2") =  "" Then

If IsEmpty(ThisWorkbook.Sheets(2).Range("H2")) Then
MsgBox "H2 Is Empty"
Else
If IsEmpty(ThisWorkbook.Sheets(2).Range("I2")) Then
MsgBox "I2 Is Empty"
Else
    MsgBox "Update Email Ready To Be Sent"
    ThisWorkbook.Sheets(2).Range("J2") = "Yes"
End If
End If
Else
ThisWorkbook.Sheets(2).Range("J2") = "Unnecessary"

End If

End If

If ThisWorkbook.Sheets(2).Range("M2") = "No" Then

If ThisWorkbook.Sheets(2).Range("L2") = "" Then
MsgBox "L2 Is Empty"
Else
If ThisWorkbook.Sheets(2).Range("L2") = "Approved with Pay" Then
MsgBox "Approved with Pay Email Sent"
ThisWorkbook.Sheets(2).Range("M2") = "Yes"
Else
If ThisWorkbook.Sheets(2).Range("L2") = "Approved without Pay" Then
MsgBox "Approved without Pay Email Sent"
ThisWorkbook.Sheets(2).Range("M2") = "Yes"
Else
If ThisWorkbook.Sheets(2).Range("L2") = "Approved in Part" Then
MsgBox "Approved in Part Email Sent"
ThisWorkbook.Sheets(2).Range("M2") = "Yes"
Else
If ThisWorkbook.Sheets(2).Range("L2") = "Referred back to the Line Manager" Then
MsgBox "Approved without Pay Email Sent"
ThisWorkbook.Sheets(2).Range("M2") = "Yes"
End If
End If
End If
End If
End If
End If

This is how I have edited the below code, but it doesn't seem to be working

这就是我编辑以下代码的方式，但它似乎不起作用

Option Explicit
Sub Test3()
End Sub

Public Function CheckIfRowIsEmpty(ByVal P2 As Range, _
                                  ByVal colsToCheck As Integer) As Boolean

    Dim isRowEmpty As Boolean
    Dim startRow As Integer
    Dim endRow As Integer
    Dim a As Integer

    isRowEmpty = True

    If IsCellEmpty(wb.Sheets(2).Range("P2")) = True Then
        startRow = P2.Column
        endRow = P2.Column + (13 - 1)

        For a = startRow To endRow
            If IsCellEmpty(P2.Offset(, a)) = False Then
                isRowEmpty = False
                Exit For
            End If
        Next a
    Else
        isRowEmpty = False
    End If
    Debug.Print CStr(isRowEmpty)
    CheckIfRowIsEmpty = CStr(isRowEmpty)

End Function


Private Function IsCellEmpty(ByVal P2 As Range) As Boolean
    If Len(P2.Value & "") < 1 Then
        IsCellEmpty = True
    Else
        IsCellEmpty = False
    End If
End Function

回答by charlespwd

I believe what you are looking for is to understand the rangeobject. When you call Range("P2")or Range("P2:Z2"), you are asking for a rangeobject.

我相信您正在寻找的是了解range对象。当您调用Range("P2")或 时Range("P2:Z2")，您是在请求一个range对象。

In short: you can think of them as a collection of variables and methods(functions).

简而言之：您可以将它们视为变量和方法（函数）的集合。

And in VBA, to use objects as variable you need to set them (a simple equality won't work):

在 VBA 中，要将对象用作变量，您需要设置它们（简单的等式不起作用）：

So in your case you'd do:

所以在你的情况下，你会这样做：

Dim rng as Range
Set rng = Range("A2")

And if you want it to be set to something else, you need to set it using a function that returns a range object. (e.g. Cells(...), Offset(...), etc.)

如果您希望将其设置为其他内容，则需要使用返回范围对象的函数进行设置。（例如Cells(...)，Offset(...)等）

And to check if a cell is empty. You'd ask for the value property and compare it to "":

并检查单元格是否为空。您会要求 value 属性并将其与 "" 进行比较：

If (rng.Value = "") then 

And if you are looking to grab the cell with the same row but in column A, you'd use Cellsinstead of Range.

如果你正在寻找抢细胞与同一行，但在A列中，你会使用Cells替代Range。

Cells(rng.Row,1).value