Looping Question

[jrobin5881]

I have a Workbook with 5 spreadsheets listing items that can be ordered by the departments in my office. The total number of items are multiplied by their cost and that figure is linked to an invoice sheet.

Here's my issue: on the invoice sheet Cells H6, H16 and H26 have a sum formula. If the amount in any of those cells exceed $10,000 then I want a message box to appear telling my user to select another vendor. I tried several different methods including looping through the cell values and cannot seem to get it to work. Here is the code I'm using.

Private Sub Worksheet_Change(ByVal Target As Range)

For Counter = 6 To 26

Set curcell = Worksheets("Invoice").Cells(Counter, 8)If Abs(curcell.Value) > 10000 Then MsgBox "Your request exceeds $10,000. You must reduce the number of items or use UNICOR as the vendor for the products requested", vbExclamation
    
    Counter = Counter + 10
    Next Counter

End Sub

 

[PHV]

For Counter = 6 To 26 Step 10
...
'Counter = Counter + 10
Next Counter

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

I changed your If statement so it would look better in the forum and generally fixed the blocking from your copy/paste.

Also I color coded other additions in red and code I commented out with the apostrophe in green like the VBA editor does by default.

In short you are mixiing different types of loops. I have shown both corrected versions (I am assuming the rest of the code is good because I don't know the Excel Object model cold).

For Counter = 6 To 26 [Red]Step 10 [/red]

     Set curcell = Worksheets("Invoice").Cells(Counter, 8)
     If Abs(curcell.Value) > 10000 Then 
        MsgBox "Your request exceeds $10,000. You must reduce the number of items or use UNICOR as the vendor for the products requested", vbExclamation
     End If
    [Green]'Counter = Counter + 10 [/Green]
Next Counter

End Sub

Or

[Red]
Counter = 6
While Counter <= 26
[/Red]
[Green]'For Counter = 6 To 26 [Red]Step 10 [/Green]

     Set curcell = Worksheets("Invoice").Cells(Counter, 8)
     If Abs(curcell.Value) > 10000 Then 
            MsgBox "Your request exceeds $10,000. You must reduce the number of items or use UNICOR as the vendor for the products requested", vbExclamation
     End If
    Counter = Counter + 10
[Green]'Next Counter[/Green]
[Red]
Wend
[/Red]
End Sub

 

[lameid]

Bit by the infamous cross post again.

I'm going to have to remember that the text wrapping varies wildly by resolution and the frames on the side. The msgbox line has almost assuredly wrapped on your screen.

 

[jrobin5881]

Thanks everyone. I tried both of Lameids solutions and it is only firing when cell "H6" is greater then 10K. It is not firing if the value of "H16" or "H26" exceed 10K.

 

[HarleyQuinn]

Really?

The first posted code fires if any of the cell's values mentioned exceed 10000.

How about posting what you've got and the values of the cells you're checking?

Regards

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[jrobin5881]

Heres a sample of the data that is in Column H on the sheet. Cells H6 and H16 are a summation of the cells above it. If I'm using the looping structure my expectation is that the message should fire off when Cell H6, Cell H16 or H26 is greater then $10K and it only works if Cell H6 is higher.



H1 TOT COST
H2
H3 $0.00
H4 $0.00
H5 $0.00
H6 $0.00
H7 $0.00
H8 $0.00
H9 $0.00
H10
H11 TOT COST
H12
H13 $35,998.00
H14 $0.00
H15 $0.00
H16 $35,998.00
H17
H18 $0.00
H19 $0.00

 

[PHV]

You still didn't post your actual code.

 

[jrobin5881]

Private Sub Worksheet_Change(ByVal Target As Range)

Counter = 6
While Counter <= 26

'For Counter = 6 To 26 [Red]Step 10

set curcell = Worksheets("Invoice").Cells(Counter, 8)
If Abs(curcell.Value) > 10000 Then
MsgBox "Your request exceeds $10,000. You must reduce the number of items or use UNICOR as the vendor for the products requested", vbExclamation

end If
    Counter = Counter + 10
'Next Counter

Wend

End Sub

 

[jrobin5881]

Okay - got it all working now. I had it in the Change function of the worksheet instead of the calaculate portion. Thank you all.