Excel VBA Check cell value and cell value above

This tutorial provides a solution to check the Excel cell value and the cell value above in Excel worksheet using VBA.

Check cell value and cell value above

This was originally a question posted in Microsoft Community. I extract it to illustrate an example how to check Excel cell value and cell value above.

Question

Given a range of 12 cells in a row filled with only 0’s and 1’s…

Would anyone know a way to return a true or false in a cell if looking in all the above cells, a 0 appeared 3 times in a row?

Thanks in advance for any ideas or thoughts.

Answer

To check whether Excel cell value and cell value above is 1 three times in a row, loop through each cell from the beginning row to end row, and check if the value is 0.

To check the cell value above, use Range(col & r + 1).Value =0

To check the cell value above, use Range(col & r + 2).Value=0

If there is an empty cell, the returned value is also 0. isEmpty() should be used to ensure the returned value is truly 0 but not  empty. But for simplicity, I did not use isEmpty() in the below codes.

Public Sub checkrow()
     'define column you want to check
     col = "A"
     'define row number you need to check
     For r = 1 To 12
         If Range(col & r).Value =0 And Range(col & r + 1).Value =0 And Range(col & r + 2).Value =0 Then
             MsgBox (True)
             Exit Sub
         End If
     Next r
     MsgBox (False)
 End Sub

 

 

Excel VBA Check cell value and cell value above