Check if current (active) cell is empty

This macro looks for the last non-empty cell in column C, then selects the cell to left of it (column B).
I wanted the macro to then check if this neighboring cell is empty or not and display a message box if True, however when I run it the box never shows up.

When I replace the X in IsEmpty(X) to a regular cell location like IsEmpty(B22) it works fine. Reason why it has to be non-fix location is because the rest of the macro I'm making is combining rows from many workbooks into a master workbook so the last row location keeps moving down.

Thanks for the help.

Sub Sample()

Range("C1").Select
Selection.End(xlDown).Select
X = ActiveCell.Offset(0, -1).Select

' Test if the value in neighboring cell is blank/empty
If IsEmpty(X) = True Then
MsgBox "Cell is empty"
End If

End Sub

Option Explicit

Sub sCheckColumnB()

With Range("C" & Rows.Count).End(xlUp).Offset(0, -1)
    MsgBox .Value
    If .Value <> "" Then
        MsgBox "The value is " & .Value
    Else
        MsgBox "The cell is empty"
    End If
End With

End Sub

Regards, TMS

Originally Posted by TMS

Option Explicit

Sub sCheckColumnB()

With Range("C" & Rows.Count).End(xlUp).Offset(0, -1)
    MsgBox .Value
    If .Value <> "" Then
        MsgBox "The value is " & .Value
    Else
        MsgBox "The cell is empty"
    End If
End With

End Sub

Regards, TMS

This worked just right, thank you.

IsEmpty doesn't check what you think it does, but instead merely verifies if a given variable has been initialized.

Instead, check for an empty string.

Sub Lateralus()

Range("C1").Select
Selection.End(xlDown).Select
X = ActiveCell.Offset(0, -1).Select

' Test if the value in neighboring cell is blank/empty
If x = "" Then MsgBox "Cell is empty"

End Sub

Which solution are you referring to?

Point TMS!

Better luck next time daff!

@daffodil: you'd really need to go for this (without the third Select)

Sub Lateralus()

Range("C1").Select
Selection.End(xlDown).Select
x = ActiveCell.Offset(0, -1)

' Test if the value in neighboring cell is blank/empty
If x = "" Then MsgBox "Cell is empty"

End Sub

And the OP's original code would have worked too, without that third Select.

Sub Sample_TMS1()

Range("C1").Select
Selection.End(xlDown).Select
x = ActiveCell.Offset(0, -1)

' Test if the value in neighboring cell is blank/empty
If IsEmpty(x) = True Then
    MsgBox "v1: Cell " & ActiveCell.Offset(0, -1).Address & " is empty"
End If

End Sub

' or, making x a range:

Sub Sample_TMS2()

Range("C1").Select
Selection.End(xlDown).Select
Set x = ActiveCell.Offset(0, -1)

' Test if the value in neighboring cell is blank/empty
If IsEmpty(x) = True Then
    MsgBox "v2: Cell " & x.Address & " is empty"
End If

End Sub

Regards, TMS

Sorry daff, not intending to make it a competition. As the two approaches were a bit different, yours nearer to the original, I was interested to see what option the OP had gone for.

Regards, TMS