Loop through cells meeting conditions

[Huyeote]

Hi guys,

Is there any quick way in VBA to code to loop through all cells in
given range meeting one or more user defined conditions? Th
Specialcells method doesn't provide input for user to define
condition. How to write concise code to do thing like.. (borrow Wher
from SQL)

For Each x in myRange Where x > 0 And x.Offset(0,-1) ="A"
.....

Next x


Thanks,

Huyeot

 

[Norman Jones]

Hi Huyeote,

Try something like:

'=============>>
Sub ATester()
Dim x As Range
Dim myRange As Range

Set myRange = Range("B1:B100")

For Each x In myRange
If x.Value > 0 _
And x.Offset(0, -1).Value = "A" Then
'do something. e.g.:
MsgBox x.Address
End If
Next x

End Sub
'=============>>

 

[alonge]

This is a snippet of my code that allowed me to go through each cell in
a column to check for certain criteria, that a certain checkbox was
chosen and a certain value in a given range of a column was chosen.

Dim Ct As Integer
Ct = 0

If ckHome2 = True Then
Range("d2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
If ActiveCell.Value = "Home" Then
Ct = Ct + 1
txtHome2.Text = Ct
End If

Hope this helps some.
Barb