I want to check to see if a value of an active cell is contained in another
range of cells elsewhere in my spreadsheet. Bascially a Match. I am trying
to pass the list of values to a variable called PMSMatchRange. Then I am
looping thougth active cells and useing the Match function on the active cell
agains the master list. I originally thought I had to Dimension the
PMSMatchRange variable as an object but this is giving me a 424 Error Object
Required when it reaches the Set statement. What am I doing wrong?

Dim PMSMatchRange As Object
vbPMSCodeList.Select
vbPMSCodeList.Range("A2").Select
vbPMSCodeList.Range(Selection, Selection.End(xlDown)).Select

Set PMSMatchRange = vbPMSCodeList.Range(Selection,
Selection.End(xlDown)).Select

For X = 1 To NumPMSCodes
ActiveCell = Range("B2").Offset(X - 1, 0)
If IsError(Application.Match(ActiveCell.Value, PMSMatchRange,
0)) Then
MsgBox ("You have PMS Codes in your import sheet that do not
exist in your PMS Master List.")
End If
Next

Hi ExcelMonkey,

I think your problem resides in the line:

Set PMSMatchRange = vbPMSCodeList.Range(Selection,
Selection.End(xlDown)).Select

Try changing this to:

Set PMSMatchRange = vbPMSCodeList.Range(Selection,
Selection.End(xlDown))

(remove the final .Select)

As for the diimming of the PMSMatchRange variable, why not declare it as a
range object?


---
Regards,
Norman



"ExcelMonkey" wrote in message
...
I want to check to see if a value of an active cell is contained in
another
range of cells elsewhere in my spreadsheet. Bascially a Match. I am
trying
to pass the list of values to a variable called PMSMatchRange. Then I am
looping thougth active cells and useing the Match function on the active
cell
agains the master list. I originally thought I had to Dimension the
PMSMatchRange variable as an object but this is giving me a 424 Error
Object
Required when it reaches the Set statement. What am I doing wrong?

Dim PMSMatchRange As Object
vbPMSCodeList.Select
vbPMSCodeList.Range("A2").Select
vbPMSCodeList.Range(Selection, Selection.End(xlDown)).Select

Set PMSMatchRange = vbPMSCodeList.Range(Selection,
Selection.End(xlDown)).Select

For X = 1 To NumPMSCodes
ActiveCell = Range("B2").Offset(X - 1, 0)
If IsError(Application.Match(ActiveCell.Value, PMSMatchRange,
0)) Then
MsgBox ("You have PMS Codes in your import sheet that do
not
exist in your PMS Master List.")
End If
Next

Thanks Norman. You have been a great help to me over the last day or so.

EM

"Norman Jones" wrote:

Hi ExcelMonkey,

I think your problem resides in the line:

Set PMSMatchRange = vbPMSCodeList.Range(Selection,
Selection.End(xlDown)).Select

Try changing this to:

Set PMSMatchRange = vbPMSCodeList.Range(Selection,
Selection.End(xlDown))

(remove the final .Select)

As for the diimming of the PMSMatchRange variable, why not declare it as a
range object?


---
Regards,
Norman



"ExcelMonkey" wrote in message
...
I want to check to see if a value of an active cell is contained in
another
range of cells elsewhere in my spreadsheet. Bascially a Match. I am
trying
to pass the list of values to a variable called PMSMatchRange. Then I am
looping thougth active cells and useing the Match function on the active
cell
agains the master list. I originally thought I had to Dimension the
PMSMatchRange variable as an object but this is giving me a 424 Error
Object
Required when it reaches the Set statement. What am I doing wrong?

Dim PMSMatchRange As Object
vbPMSCodeList.Select
vbPMSCodeList.Range("A2").Select
vbPMSCodeList.Range(Selection, Selection.End(xlDown)).Select

Set PMSMatchRange = vbPMSCodeList.Range(Selection,
Selection.End(xlDown)).Select

For X = 1 To NumPMSCodes
ActiveCell = Range("B2").Offset(X - 1, 0)
If IsError(Application.Match(ActiveCell.Value, PMSMatchRange,
0)) Then
MsgBox ("You have PMS Codes in your import sheet that do
not
exist in your PMS Master List.")
End If
Next

On Fri, 19 Aug 2005 04:22:02 -0700, "ExcelMonkey"
wrote:

I want to check to see if a value of an active cell is contained in another
range of cells elsewhere in my spreadsheet. Bascially a Match. I am trying
to pass the list of values to a variable called PMSMatchRange. Then I am
looping thougth active cells and useing the Match function on the active cell
agains the master list. I originally thought I had to Dimension the
PMSMatchRange variable as an object but this is giving me a 424 Error Object
Required when it reaches the Set statement. What am I doing wrong?

Dim PMSMatchRange As Object
vbPMSCodeList.Select
vbPMSCodeList.Range("A2").Select
vbPMSCodeList.Range(Selection, Selection.End(xlDown)).Select

Set PMSMatchRange = vbPMSCodeList.Range(Selection,
Selection.End(xlDown)).Select

For X = 1 To NumPMSCodes
ActiveCell = Range("B2").Offset(X - 1, 0)
If IsError(Application.Match(ActiveCell.Value, PMSMatchRange,
0)) Then
MsgBox ("You have PMS Codes in your import sheet that do not
exist in your PMS Master List.")
End If
Next

Do you need to do this programatically?

The way I'm interpreting your question, it seems like a standard
vlookup formula will give you the information you want.

I'm reading it that you have a pre-defined list of valid codes, and
that after bringing data into an 'Import' sheet, you want to check all
of the codes you've imported against your pre-defined list.

In which case assuming your pre-defined list has a range name
"PMSCodeList", and your import sheet has codes starting inn say B1,
then in a spare column on the import sheet, Say E1, enter the
following and copy it down the imported list.

=if(iserror(vlookup(B1,PMScodelist,1,false)),"You have PMS Codes not
in PMS Master List","Valid Code)


If you want to do it programatically then it should be a fairly simple
task to embed the vlookup formula in a For Next Loop.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________