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

Unable to get Match property of the WorksheetFunction class
 

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

Unable to get Match property of the WorksheetFunction class
 

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

Unable to get Match property of the WorksheetFunction class
 

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

Unable to get Match property of the WorksheetFunction class
 

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

Unable to get Match property of the WorksheetFunction class
 

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

Unable to get Match property of the WorksheetFunction class
 

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

Unable to get Match property of the WorksheetFunction class
 

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

Unable to get Match property of the WorksheetFunction class
 

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