Dave Peterson wrote:
Yeah, but that wasn't the most current version of the code.

If you have the validation lists on each worksheet, then give each range the
same name--make it a worksheet level name.

Then this line:

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

Change datavalidationnamedrange to the name you used.

You may want to take a look at the sequence of posts and look at how the code
evolved.

prkhan56 wrote:

Hi Dave,

The code I posted was given by you only...which works ...however other
codes do not work.. I just posted to give you an idea about it.

Sorry Dave, My Workbook name is Equipment Inventory Details.xls which
has many sheets as mentioned in my previous post. Each sheet has its
own data/validation....

Do you mean I have to have another sheet with all the
Data/Validation...that would not be possible as I keep on increasing my
data day by day on each individual sheets.

Kindly guide me through this now.

Thanks for all the effort you have taken to help me out.

You are a real help

Rashid Khan

--

Dave Peterson

Hi Dave,

I have following as worksheet level range names:
Data (dynamic - currently $E$2:$BN$255) - defined on PC Sheet
ID (dynamic - currently $E$2:$E$255) - defined on PC Sheet
PrintData (variable) - defined on PCDetails Sheet

This is how my codes look 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
Set myRng = wks.Range("Data")
'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("PrintData").PrintOut preview:=True
End If
End If
Next myCell
End Sub

I am running the macro from the PcDetails Sheet and I feel that the
following line is causing some trouble.

Set wks = ActiveSheet

Pardon me if I am wrong, but you are an expert to see if I am right.

Thanks once again for all the help you have rendered so far.

Rashid Khan