How to search for values in a range?

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
32
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello everybody!

I created some code where I individually compare the cells in a range, checking if at least one of them meets the criteria I'm looking for and I realize that not only does it make my code repeatable, it also makes it bigger.

I would like to declare a range and then check if at least one of your cells fits what I'm looking for.

Examples:

1) check if in Range (A1: E1) there is at least one empty cell;
2) check if in Range (A1: E1) there is at least one cell in which the fill is not white;
3) check if in Range (A5: E5) there is a value other than Blue
4) check if the Range (

If I know how to make this comparison in a range, I will save time and will certainly be more agile.

Thank you in advance, see you next time.




cp3.PNG
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
This might do it in some sort of way.

Code:
Sub RngComp()
Dim lr As Long, ary(5) As String, i As Long, j As Long, k As Long
'assumes the last data row in A column HAS a value
 lr = Sheets("RngComp").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lr
  ary(0) = Cells(i, 1)
  ary(1) = Cells(i, 2)
  ary(2) = Cells(i, 3)
  ary(3) = Cells(i, 4)
  ary(4) = Cells(i, 5)

 For j = 0 To 4
  If ary(j) = "" Then
  MsgBox "At least one empty cell in row  " & i
  Exit For
  Else
  End If
 Next j
Next i

For i = 1 To lr
 For k = 1 To 5
 If Cells(i, k).Interior.ColorIndex <> -4142 Then
  MsgBox "At least one cell is does not have a white interior color in row " & i
 Exit For
 Else
 End If
 Next k
Next i

For i = 1 To lr
 For k = 1 To 5
 If Cells(i, k) <> "Blue" Then
  MsgBox "At least one cell is does not have Blue as the value in row " & i
 Exit For
 Else
 End If
 Next k
Next i

End Sub
 
Upvote 0
Solution
This might do it in some sort of way.

Code:
Sub RngComp()
Dim lr As Long, ary(5) As String, i As Long, j As Long, k As Long
'assumes the last data row in A column HAS a value
lr = Sheets("RngComp").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lr
  ary(0) = Cells(i, 1)
  ary(1) = Cells(i, 2)
  ary(2) = Cells(i, 3)
  ary(3) = Cells(i, 4)
  ary(4) = Cells(i, 5)

For j = 0 To 4
  If ary(j) = "" Then
  MsgBox "At least one empty cell in row  " & i
  Exit For
  Else
  End If
Next j
Next i

For i = 1 To lr
For k = 1 To 5
If Cells(i, k).Interior.ColorIndex <> -4142 Then
  MsgBox "At least one cell is does not have a white interior color in row " & i
Exit For
Else
End If
Next k
Next i

For i = 1 To lr
For k = 1 To 5
If Cells(i, k) <> "Blue" Then
  MsgBox "At least one cell is does not have Blue as the value in row " & i
Exit For
Else
End If
Next k
Next i

End Sub
Very cool! You helped me a lot, buddy!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top