Unable to get Match property of the WorksheetFunction class

[C++User]

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

 

[Guest]

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

 

[C++User]

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.

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

 

[Guest]

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

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.

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

 

[Guest]

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

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.

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++User]

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

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]

 

[Tom Ogilvy]

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

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

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++User via OfficeKB.com]

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)

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]

 

[C++User via 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

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]