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

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *