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

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.Column+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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>