Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping problem........
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping Problem | Excel Programming | |||
Looping problem | Excel Programming | |||
Looping Problem . . .Argh! | Excel Programming | |||
If Then Else looping problem | Excel Programming | |||
Looping Problem | Excel Programming |