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

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

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

I the activesheet is pcdetails, then I don't see a problem with that line.

What goes wrong?

prkhan56 wrote:

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

--

Dave Peterson

Hi Dave,
I get two input boxes...and after inputting the numbers I get the
following error.

I get Run time error '1004'
Application-defined or object-defined error.

Rashid Khan

I'm confused...

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

I thought you said all your data validation lists were on the worksheet that had
the data|validation cell.

I guess I still don't know where the ranges are for all the sheets.

If they're on the same sheet as the data validation cell, use the sheet level
names. If the list is on another sheet, then use the global name--but include
the name of the sheet with the data|validation (like in one of the earlier
versions).



prkhan56 wrote:

Hi Dave,
I get two input boxes...and after inputting the numbers I get the
following error.

I get Run time error '1004'
Application-defined or object-defined error.

Rashid Khan

--

Dave Peterson

Hi Dave,
Thanks for the quick and prompt reply and sorry for the confusion...I
think now I am more confused than you are.

I am replying to each of your query:

I thought you said all your data validation lists were on the worksheet
that had
the data|validation cell.

Data Validation List is on PC Sheet and Data Validation Cell is on PC
Details Sheet.
I have used the Global Range name to show me the drop down.

I guess I still don't know where the ranges are for all the sheets.

As said in my previous post...I am working in sheet pairs...like PC
with Pc Details, Printer with Printer Details, Monitor with Monitor
Details

If they're on the same sheet as the data validation cell, use the sheet
level
names. If the list is on another sheet, then use the global name--but
include
the name of the sheet with the data|validation (like in one of the
earlier
versions).

But Data|Validation does not allow me to put something like
Allow - List - PC!ID

It says "you may not use reference to other worksheet for data
validation criteria"

The following is just an example of the PC sheet...there are many other
sheets... as I had mentioned in my previous post that they are all in
pairs (viz. PC goes with PC Details, Printer goes with Printer
Details...and so on)

Below is just an example of the PC Sheet:

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

Am I clear now?

Rashid Khan