Count Cells in Range
 

I have a named range (non contiguous cells) going down several columns called
BidCatNum. Most of these cells contain the letter "Y". For each cell that
contains the letter "Y", I want to write code to perform an action on the
cell immediately to the right of it.

I'm not too experienced with the "For each cell in this range, do this"
lingo. Could someone get me started? Thanks!

Count Cells in Range
 

Sub ProcessBidCatNum()
Dim cell as Range, cell1 as Range
for each cell in Range("BidCatNum")
if lcase(cell.value) = "y" then
set cell1 = cell.offset(0,1)
' perform action using cell1
end if
Next
End Sub

--
Regards,
Tom Ogilvy

"Steve C" wrote in message
...
I have a named range (non contiguous cells) going down several columns
called
BidCatNum. Most of these cells contain the letter "Y". For each cell
that
contains the letter "Y", I want to write code to perform an action on the
cell immediately to the right of it.

I'm not too experienced with the "For each cell in this range, do this"
lingo. Could someone get me started? Thanks!

Count Cells in Range
 

This should be close...

Sub DoStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String

Set rngToSearch = Range("BidCatNum")
Set rngFound = rngToSearch.Find(What:="Y", _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
Call ChangeStuff(rngFound.Offset(0, 1))
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If

End Sub

Sub ChangeStuff(ByVal Target As Range)
MsgBox Target.Address
End Sub

--
HTH...

Jim Thomlinson


"Steve C" wrote:

I have a named range (non contiguous cells) going down several columns called
BidCatNum. Most of these cells contain the letter "Y". For each cell that
contains the letter "Y", I want to write code to perform an action on the
cell immediately to the right of it.

I'm not too experienced with the "For each cell in this range, do this"
lingo. Could someone get me started? Thanks!

Count Cells in Range
 

Tom & Jim: Thanks a ton!


"Jim Thomlinson" wrote:

This should be close...

Sub DoStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String

Set rngToSearch = Range("BidCatNum")
Set rngFound = rngToSearch.Find(What:="Y", _
LookIn:=xlValues, _
LookAt:=xlWhole)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
Call ChangeStuff(rngFound.Offset(0, 1))
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If

End Sub

Sub ChangeStuff(ByVal Target As Range)
MsgBox Target.Address
End Sub

--
HTH...

Jim Thomlinson


"Steve C" wrote:

I have a named range (non contiguous cells) going down several columns called
BidCatNum. Most of these cells contain the letter "Y". For each cell that
contains the letter "Y", I want to write code to perform an action on the
cell immediately to the right of it.

I'm not too experienced with the "For each cell in this range, do this"
lingo. Could someone get me started? Thanks!