What is the name of the worksheet that contains all the data|validation lists?

I used "datavalidationlists" since I didn't know.

Your values still look like xxx###yyyyy?

If yes, maybe checking those ### to see if they're between the numbers you type
in:

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

prkhan56 wrote:

Hi Dave,
So now I have Workbook level name and Sheet level names eg PCID
(global) and ID (sheet level), for all my sheets.

As you suggested I have define all my range names beginning with the
worksheet name...eg PC has PCID, Printer has PrinterID and so on.

But what is "datavalidationlists". Is this a new range name?

Because I pasted the following code and it gives me "Runtime error
'9'
Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Set wks = ActiveSheet
Set myRng = Worksheets("datavalidationlist¬s").Range(wks.Name &
"ID")
For Each myCell In myRng.Cells
wks.Range("ID").Value = myCell.Value
Application.Calculate 'just in case
wks.Range("DetailsPrint").Prin¬tOut preview:=True
Next myCell
End Sub
And other thing which your previous code had is missing in the above
code ...where two Input boxes used to pop-up and ask for the beginning
and end number...

With my limited knowledge the above mentioned code would run and print
thru the complete list (which is what I don't require). I need to
print certain records only and not all at one time...

I was thinking of having a S.No. Cell at the top right hand corner
(outside the print area obviously) with a Vlookup to give me an
indication of the record I am currently on and then check the beginning
and end number of record needed for printing and then put the numbers
accordingly in the beginning and end Input box.

Would you be kind enough to look into this?
May be you could have some other expert thought on this!
Thanks for all the time and effort you have taken to help me out.

Rashid Khan

--

Dave Peterson