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

--
Huyeot
-----------------------------------------------------------------------
Huyeote's Profile: http://www.excelforum.com/member.php...fo&userid=1389
View this thread: http://www.excelforum.com/showthread.php?threadid=47572

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
'=============

---
Regards,
Norman



"Huyeote" wrote in
message ...

Hi guys,

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

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

Next x


Thanks,

Huyeote


--
Huyeote
------------------------------------------------------------------------
Huyeote's Profile:
http://www.excelforum.com/member.php...o&userid=13894
View this thread: http://www.excelforum.com/showthread...hreadid=475728

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


--
alonge
------------------------------------------------------------------------
alonge's Profile: http://www.excelforum.com/member.php...o&userid=27874
View this thread: http://www.excelforum.com/showthread...hreadid=475728