ExcelBanter - Looping problem........

Personally, I'd use another column (new column B??) and put all the error
messages in that column. Format the error messages so that they're big bright
bold red:

=if(a1<"err","","Please check qty!")

Then you could just look for ERR in your code:

if application.countif(activesheet.range("a1:A10"),"e rr")0 then
msgbox "Please fix the errors!
else
'do the printpreview.
end if

But if you want code, maybe something like:

Option Explicit
Sub PRINTDoc()

Dim myName As String
Dim RngToCheck As Range
Dim myCell As Range
Dim myMsg As String
Dim iCtr As Long
Dim myWarnings As Variant
Dim HowMany As Long

myWarnings = Array("Qty", _
"Account", _
"Value")

HowMany = UBound(myWarnings) - LBound(myWarnings) + 1

With ActiveSheet
myName = .Range("b1").Value ' displays username
Set RngToCheck = .Range("a1").Resize(HowMany, 1)

iCtr = LBound(myWarnings)
For Each myCell In RngToCheck.Cells
If UCase(myCell.Value) = "ERR" Then
myMsg = myMsg & vbLf & myWarnings(iCtr)
End If
iCtr = iCtr + 1
Next myCell

If myMsg < "" Then
myMsg = myName & ", Please check: " & vbLf & myMsg
MsgBox myMsg
Else
.PageSetup.PrintArea = "$A$59:$J$116"
.PrintPreview
End If
End With

End Sub

Steve Jones wrote:

Hi

I would like to have a loop in the code below so that it only goes to Print
Preview/Print when A1,A2 & A3 don't equal "ERR". ( This is only an example
the actual range would be "A1:A10").
I would still like the messages to display so the user knows what they need
to look at.

I'm not sure whether or not it is For/Next or Do Until or both. I'd be
grateful for your help.

Regards

Steve

Sub PRINTDoc()

Dim Name As String
Name = Range("b1").Value ' displays username

Application.ScreenUpdating = False

If Range("a1").Value = "ERR" Then MsgBox (Name & ", Please check Qty.")
If Range("a2").Value = "ERR" Then MsgBox (Name & ", Please check Account
No.")
If Range("a3").Value = "ERR" Then MsgBox (Name & ", Please check
Value.")

ActiveSheet.PageSetup.PrintArea = "$A$59:$J$116"
' ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWindow.SelectedSheets.PrintPreview

End Sub

--

Dave Peterson