looping for a condition
 

I will try to explain this as best as possible. Beginning VBA user and
have excel 2003. What I would like to do is this.

I need code to look at a field in a row, for example

If activecell.offset(0,5).value = yes then
"msg box to ask a question if it wants to print out"
End If


My problem is the following.

I have sheet1

Row 20 = Original 4 oz
Row 21 = Original 9 oz
Row 22 = Original 20 oz
Row 23 = Original 48 oz


The code loops thru each row and checks to see if
activecell.offset(0,5).value = yes
If yes then it prompts for printout with a message box.

This works great if I only have 1 item.

But for the above situation I don't want the message box to prompt for
the printout until it has looped thru the last of the group (in this
case Original 48 oz). Then prompt for a message box


Sorry for the lousy explanations.


I would appreciate any help

thanks Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=472190

looping for a condition
 

Not sure why you are looping.

It sounds like if there is a single "yes" anywhere, than you want a
printout. For this you can use

If Worksheetfunction.Countif(Columns(ActiveCell.Colum n+5),"yes")0 then
' do your stuff
End If

or you can loop
Dim rw as Long
For rw = 20 to 23
If Cells(rw, 6).Text = "yes" then
'do your stuff
end if
Next

or
Dim cel as Range
For each cel in Range("F20:F23")
If cel = "yes" then
'do your stuff
end if
Next


for each loop you can set a variable x to 0 and keep adding to it

x = 0
For each
if "yes" then
x = x+1
end if
next

If x 0 then
' do your stuff
End if

A lot depends on how you are selecting the cell to test.

Post back...
--
steveB

Remove "AYN" from email to respond
"jhahes" wrote in
message ...

I will try to explain this as best as possible. Beginning VBA user and
have excel 2003. What I would like to do is this.

I need code to look at a field in a row, for example

If activecell.offset(0,5).value = yes then
"msg box to ask a question if it wants to print out"
End If


My problem is the following.

I have sheet1

Row 20 = Original 4 oz
Row 21 = Original 9 oz
Row 22 = Original 20 oz
Row 23 = Original 48 oz


The code loops thru each row and checks to see if
activecell.offset(0,5).value = yes
If yes then it prompts for printout with a message box.

This works great if I only have 1 item.

But for the above situation I don't want the message box to prompt for
the printout until it has looped thru the last of the group (in this
case Original 48 oz). Then prompt for a message box


Sorry for the lousy explanations.


I would appreciate any help

thanks Josh


--
jhahes
------------------------------------------------------------------------
jhahes's Profile:
http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=472190