Change this section:

Case Else:
MsgBox "design error with worksheet: " & wks.Name

To:

Case Else:
MsgBox "design error with worksheet: " & wks.Name
exit sub

This won't fix your problem, but will stop the error from occurring.

But I thought your code should have been more like:

Case Is = "pc"
Set myRng = Worksheets("pc details").Range("validationrangenamehere!")

=========
And if you get this message:
design error with worksheet PC Details

That means that you're clicking on a button on PC Details. I thought that you
printed PC based on the data in PC details.

If you are printing directly from "PC Details", then you'll need to add that
"pair" to your list:

Case Is = "pc details"
Set myRng = Worksheets("whatworksheetgoeshere") _
.Range("validationrangenamehere!")

Don't forget to update the worksheet name and the range names to what matches
your project.


prkhan56 wrote:

Hi Dave,
Sorry for all the trouble I have been giving you.

When I run the code... I get two Input boxes, then a message box saying
"design error with worksheet PC Details and after I click Ok on this
message box I get another error message in VBE saying:
Run-time error '91' Object variable or With block variable not set

I have total 8 Pairs (16 Sheets) + 1 Sheet for Party Code and 1 Sheet
for Eqpt Code - total 18 sheets.

I have included the 8 pairs in your code.

Following is the complete code now:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim StartVal As Long
Dim EndVal As Long
Dim TempVal As Long
Dim iCtr As Long
StartVal = CLng(Application.InputBox(Prompt:="Start with", _
Default:=1, Type:=1))
If StartVal = 0 Then
Exit Sub
End If
EndVal = CLng(Application.InputBox(Prompt:="End with", _
Default:=StartVal + 1, Type:=1))
If EndVal = 0 Then
Exit Sub
End If
If EndVal < StartVal Then
TempVal = StartVal
StartVal = EndVal
EndVal = TempVal
End If
Set wks = ActiveSheet
Select Case LCase(wks.Name)
Case Is = "pc"
Set myRng = Worksheets("pc details").Range("printarea")
Case Is = "printer"
Set myRng = Worksheets("printer form").Range("printarea")
Case Is = "monitor"
Set myRng = Worksheets("monitor form").Range("printarea")
Case Is = "switch"
Set myRng = Worksheets("switch form").Range("printarea")
Case Is = "router"
Set myRng = Worksheets("router form").Range("printarea")
Case Is = "firewall"
Set myRng = Worksheets("firewall form").Range("printarea")
Case Is = "modem"
Set myRng = Worksheets("modem form").Range("printarea")
Case Is = "scanner"
Set myRng = Worksheets("scanner form").Range("printarea")
Case Else:
MsgBox "design error with worksheet: " & wks.Name

End Select

'xxx???yyyyy
For Each myCell In myRng.Cells
If IsNumeric(Mid(myCell.Value, 4, 3)) Then
If StartVal <= Val(Mid(myCell.Value, 4, 3)) _
And EndVal = Val(Mid(myCell.Value, 4, 3)) Then
wks.Range("ID").Value = myCell.Value
Application.Calculate 'just in case
wks.Range("PRINTAREA").PrintOut preview:=True
End If
End If
Next myCell
End Sub

If you permit I can send you my file!

Thanks once again for all the support and help you have extended to me.

Rashid Khan

--

Dave Peterson