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

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

excel vba row

Solution 1:^[1]

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

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):

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.)

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

If (rng.Value = "") then

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

Cells(rng.Row,1).value

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	charlespwd

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

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]