Lookup up active cell value in passed range

[ExcelMonkey]

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

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[ExcelMonkey]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Richard Buttrey]

On Fri, 19 Aug 2005 04:22:02 -0700, "ExcelMonkey"
<[email protected]> 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
__________________________