Unable to get Match property of the WorksheetFunction class

I have two workbooks; one contains a list of mtrl Lot numbers in one column
and in the next wether it was accepted or not; the other worksheet is a form
in which the end user fills out. I want to check wether the lot numbers in
the form are in the summary or not and if it was accepted or not. Once I have
this info I need to let the end user by coloring the cell with the lot number
on the form.

This is what I've got now:
Sub Test
Dim rR As Range
Dim rRM As Range
Dim dDM As Double
Dim strValue As String

Application.Workbooks.Open("M:\BodyPrep\ALL BATCH MATERIAL TESTS &
RESULTS\- RAW SUMMARY - COA's.xls", False, False).Activate

Application.Workbooks("- RAW SUMMARY - COA's.xls").Worksheets("RAW MTRL
COA's").Activate
Range("$D$2:$E$3000").Select
Set rR = Selection

Range("$D$2:$D$3000").Select
Set rRM = Selection

For x = 10 To 11
strValue = Application.ThisWorkbook.Worksheets("Data Entry").Range
("E" & x).Value
Application.Workbooks("- RAW SUMMARY - COA's.xls").Activate

dDM = Application.WorksheetFunction.Match(strValue, rRM)

If Application.WorksheetFunction.Index(rR, dDM, 3) = "VALID" Then
Application.ThisWorkbook.Range("E" & x).Interior.ColorIndex
(3)
End If
Next x
End Sub

What are you asking? How to handle the error when the value is not found?

--
Regards,
Tom Ogilvy


"C++User" wrote:

> I have two workbooks; one contains a list of mtrl Lot numbers in one column
> and in the next wether it was accepted or not; the other worksheet is a form
> in which the end user fills out. I want to check wether the lot numbers in
> the form are in the summary or not and if it was accepted or not. Once I have
> this info I need to let the end user by coloring the cell with the lot number
> on the form.
>
> This is what I've got now:
> Sub Test
> Dim rR As Range
> Dim rRM As Range
> Dim dDM As Double
> Dim strValue As String
>
> Application.Workbooks.Open("M:\BodyPrep\ALL BATCH MATERIAL TESTS &
> RESULTS\- RAW SUMMARY - COA's.xls", False, False).Activate
>
> Application.Workbooks("- RAW SUMMARY - COA's.xls").Worksheets("RAW MTRL
> COA's").Activate
> Range("$D$2:$E$3000").Select
> Set rR = Selection
>
> Range("$D$2:$D$3000").Select
> Set rRM = Selection
>
> For x = 10 To 11
> strValue = Application.ThisWorkbook.Worksheets("Data Entry").Range
> ("E" & x).Value
> Application.Workbooks("- RAW SUMMARY - COA's.xls").Activate
>
> dDM = Application.WorksheetFunction.Match(strValue, rRM)
>
> If Application.WorksheetFunction.Index(rR, dDM, 3) = "VALID" Then
> Application.ThisWorkbook.Range("E" & x).Interior.ColorIndex
> (3)
> End If
> Next x
> End Sub
>

It's not the value is not there(the test value is even the first value in the
range), it's that I'm getting a runtime error and I want to fix it but once I
get the code to find a known value would also like to handle the error when
the value is not found.

Tom Ogilvy wrote:
>What are you asking? How to handle the error when the value is not found?
>
>> I have two workbooks; one contains a list of mtrl Lot numbers in one column
>> and in the next wether it was accepted or not; the other worksheet is a form
>[quoted text clipped - 34 lines]
>> Next x
>> End Sub

--
C is great... C++ is God's sin

Well, demo'ing in the immediate window:

Range("$D$2:$D$3000").Select
Set rRM = Selection
x = 10
strValue = Application.ThisWorkbook.Worksheets("Data Entry").Range("E" &
x).Value
? strValue
A
dDM = Application.WorksheetFunction.Match(strValue, rRM)
? dDM
26

shows that it works.

--
Regards,
Tom Ogilvy


"C++User" wrote:

> It's not the value is not there(the test value is even the first value in the
> range), it's that I'm getting a runtime error and I want to fix it but once I
> get the code to find a known value would also like to handle the error when
> the value is not found.
>
> Tom Ogilvy wrote:
> >What are you asking? How to handle the error when the value is not found?
> >
> >> I have two workbooks; one contains a list of mtrl Lot numbers in one column
> >> and in the next wether it was accepted or not; the other worksheet is a form
> >[quoted text clipped - 34 lines]
> >> Next x
> >> End Sub
>
> --
> C is great... C++ is God's sin
>

I will add that you haven't specified the third argument to Match, so that
could be problematic. Without a third argument, it assumes the data is
sorted ascending. You might try

dDM = Application.WorksheetFunction.Match(strValue, rRM,0)


--
Regards,
Tom Ogilvy


"C++User" wrote:

> It's not the value is not there(the test value is even the first value in the
> range), it's that I'm getting a runtime error and I want to fix it but once I
> get the code to find a known value would also like to handle the error when
> the value is not found.
>
> Tom Ogilvy wrote:
> >What are you asking? How to handle the error when the value is not found?
> >
> >> I have two workbooks; one contains a list of mtrl Lot numbers in one column
> >> and in the next wether it was accepted or not; the other worksheet is a form
> >[quoted text clipped - 34 lines]
> >> Next x
> >> End Sub
>
> --
> C is great... C++ is God's sin
>

I'm still coming up with the same error after adding your recommandation.

Tom Ogilvy wrote:
>I will add that you haven't specified the third argument to Match, so that
>could be problematic. Without a third argument, it assumes the data is
>sorted ascending. You might try
>
>dDM = Application.WorksheetFunction.Match(strValue, rRM,0)
>
>> It's not the value is not there(the test value is even the first value in the
>> range), it's that I'm getting a runtime error and I want to fix it but once I
>[quoted text clipped - 8 lines]
>> >> Next x
>> >> End Sub

--
C is great... C++ is God's sin

Try

Dim strValue as Variant ' rather than string

strValue = Application.Worksheets("Data Entry").Range("E" & x).Value
if iserror(strValue) then
msgbox "Value not found"
exit sub
end if


If the value isn't found it won't raise a 1004 error - it returns a
worksheet type error. This will at least give some indication if that is
the problem or not.

--
Regards,
Tom Ogilvy


"C++User" <u21442@uwe> wrote in message news:5fa8ce49fdffc@uwe...
> I'm still coming up with the same error after adding your recommandation.
>
> Tom Ogilvy wrote:
> >I will add that you haven't specified the third argument to Match, so
that
> >could be problematic. Without a third argument, it assumes the data is
> >sorted ascending. You might try
> >
> >dDM = Application.WorksheetFunction.Match(strValue, rRM,0)
> >
> >> It's not the value is not there(the test value is even the first value
in the
> >> range), it's that I'm getting a runtime error and I want to fix it but
once I
> >[quoted text clipped - 8 lines]
> >> >> Next x
> >> >> End Sub
>
> --
> C is great... C++ is God's sin

For some strange reason this works but now I'm having a problem with setting
the
Application.ThisWorkbook.Worksheet("Data Entry").Range("E" & x).Interior.
ColorIndex (3)

Tom Ogilvy wrote:
>Try
>
>Dim strValue as Variant ' rather than string
>
>strValue = Application.Worksheets("Data Entry").Range("E" & x).Value
>if iserror(strValue) then
> msgbox "Value not found"
> exit sub
>end if
>
>If the value isn't found it won't raise a 1004 error - it returns a
>worksheet type error. This will at least give some indication if that is
>the problem or not.
>
>> I'm still coming up with the same error after adding your recommandation.
>>
>[quoted text clipped - 9 lines]
>> >> >> Next x
>> >> >> End Sub

--
C is great... C++ is God's sin

Message posted via http://www.officekb.com

Never mind Tom I got. Thanks for all your help. One last thing, do you know a
way to check to see if a workbook is open like:

if Workbook.IsOpen = true

and do you know of a good VBA for Excel reference book.

Thanks again

Tom Ogilvy wrote:
>Try
>
>Dim strValue as Variant ' rather than string
>
>strValue = Application.Worksheets("Data Entry").Range("E" & x).Value
>if iserror(strValue) then
> msgbox "Value not found"
> exit sub
>end if
>
>If the value isn't found it won't raise a 1004 error - it returns a
>worksheet type error. This will at least give some indication if that is
>the problem or not.
>
>> I'm still coming up with the same error after adding your recommandation.
>>
>[quoted text clipped - 9 lines]
>> >> >> Next x
>> >> >> End Sub

--
C is great... C++ is God's sin

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1