strange printing/looping problem

[Omar78]

Hello,

i have 2 sheets

Sheet1 has 3 columns - Class, Attendance, Target
Sheet2 has a label whose caption depends on the data from sheet1. ther
is also a command button on sheet2

Private Sub CommandButton1_Click()
Dim n
n = 1 'assumes first row of your data on Sheet1
Sheets("Sheet1").Select

Do Until n = 4 'only 3 rows of data at the moment

If Cells(n, 2) >= Cells(n, 3) Then
Sheets("Sheet2").Select
Label1.Caption = "congrats" & " " & Sheets("Sheet1").Cells(n
3)
Else
Sheets("Sheet2").Select
Label1.Caption = "bad luck" & " " & Sheets("Sheet1").Cells(n
3)
End If
ActiveSheet.PrintPreview
Sheets("Sheet1").Select

n = n + 1

Loop

End Sub

now when i click on the button i get a print preview box with th
target for row 1..when i click on Close .. the print preview comes bac
with the same data! it should come back with data from row 2..i clic
close again.. the 3rd print preview appears again with the same dat
(data from row 1).

i just cant see what could be the problem here.. i have tried the Wai
method..but no luck..it does appear to be looping through because onc
the program has finished the label in sheet2 displays the data fro
row3.

would appreciate any input.

 

[Charles]

Omar,

I've look at your request. I'm confused as to what you are trying t
accomplish. In your loop if you are trying to read the 2'd row and th
3'rd row during each loop you need to set up a loop for sheet2 to rea
each row. If you could give more detail as to what your trying to d
maybe I or someone else could help you.

Charle

 

[Omar78]

thanks for looking chrales!

ok basically this is what im trying to do. i have 3 columns of data o
sheet1 class, attendance and target. on sheet 2 i have a label and
command button. what im want is that when you press the command butto
excel will check to see if the data in column 2 (attendance) is highe
than column3 (target) on sheet1. once this check has been done the
the label on sheet2 will change depending on whether a class has me
its target or not. then sheet 2 will be printed out and the progra
should back to the start of the loop to check for row 2 on sheet
etc.. until there is no more data in row1, column1 on sheet1.

i have attached my worksheet to help you understand it.

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=56791

 

[Omar78]

hmm.. this is a strange one.. i placed a break at the Loop ... and whe
i continue on from the loop it works! but when i take the break out an
run it.. it doesnt..i just keep on getting the data from Row 1 ..
feel like im just typing to myself in this discussion.

 

[Charles]

Omar78,

Sorry I had other things come up. Here is my solution.
I placed you msg on sheet2 rang a1. For some reason I couldn't get th
lable to change after each print.

Hope this works for you.

Option Explicit
Public x As Long
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim rng As Range
Dim ans As String
Set rng = Worksheets("Sheet1").Cells(1, 1).CurrentRegion
For x = 1 To rng.Rows.Count
If rng(x, 2) >= rng(x, 3) Then
Worksheets("Sheet2").Activate
Range("A1").Value = "congrats" & " " & rng(x, 3)
ans = MsgBox("Do You want to print?" & " congrats" & " "
rng(x, 3), vbYesNo)
If ans = vbYes Then
With Sheets("sheet2")
Worksheets("Sheet2").PrintOut
Range("A1").Value = ""
End With
End If
Else
Sheets("Sheet2").Activate
Range("A1").Value = "BadLuck" & " " & rng(x, 3)
ans = MsgBox("Do You want to print?" & " BadLuck " & " "
rng(x, 3), vbYesNo)
If ans = vbYes Then
With Sheets("sheet2")
Worksheets("Sheet2").PrintOut
Range("A1").Value = ""
End With
End If
End If
Next
End Su

 

[Omar78]

Charles: Many thanks for your code, it works great.. however there wil
be about 20 classes.. so clicking on each one individually is no
ideal... i will have a go at adjusting the code, if i get stuck... i'l
get back to you.. thanks again