First, if you're using the code that you posted, it isn't the most current.

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

Set myRng = Worksheets("datavalidationlists").Range(wks.Name & "ID")
'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("DetailsPrint").PrintOut preview:=True
End If
End If
Next myCell

End Sub

Second, what worksheet holds the lists that you use for data validation?

Change "datavalidationlists" on this line to the name that holds all the data
validation lists.


Set myRng = Worksheets("datavalidationlists").Range(wks.Name & "ID")



prkhan56 wrote:

Hi Dave,

Sorry for all the trouble you have taken to help me...your code shows
the 'Start with' and 'End with' Input box and then gives an
error as follows:

Run Time error '9' - Subscript out of range.

I am again giving you a detail of my Workbook and Range Names.

1) Workbook Name: Equipment Inventory Details.xls
2) Sheet Names shown in pairs: PC/PC Form, Printer/Printer Form,
Monitor/Monitor Form .....and so on...
3) Local Range Names defined as ID and Data on all sheets (PC, Printer,
Monitor ....)
4) Local Range Names defined as PrintArea on all sheets (PC Form,
Printer Form, Monitor Form ....)
5) Global Range Name defined as PcID, PcData, PrinterID, PrinterData,
MonitorID, MonitorData....
6) All IDs are like xxx###yyyyy (where ### is numeric)

All xxxForm sheets are designed like a form and Cell C5 on these sheets
is used with conditional formatting to display the ID from relevant
sheet e.g PcID from PC Sheet ... PrinterID from Printer Sheet and so
on...

I need to print from a certain ID to certain ID...

FYI, the following code suggested by you is working for one of my
worksheet with 'no local sheet range names' it shows my counter
formula in Cell K1 ...as 1 then 2 then 3 .. but after the macro is
run... I loose my Vlookup formula in K1 (the counter cell) .. the
counter cell is defined outside the print area. Print Area is defined
from Row 2 onwards.

Your code:

Option Explicit
Sub testme()
Dim StartVal As Long
Dim EndVal As Long
Dim TempVal As Long
Dim iCtr As Long
Dim wks As Worksheet
Set wks = Worksheets("PC DETAILS")
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
For iCtr = StartVal To EndVal
wks.Range("PCLIST").Value = iCtr
'Application.Calculate 'just in case
wks.Range("PcDetailsPrint").PrintOut
Next iCtr
End Sub

If with the above code ...I don't loose my Vlookup in Cell K1 then I
think that would also be sufficient my need...Hope I am clear now.

Thanks a lot once again for all the time and help.

Rashid Khan

--

Dave Peterson