Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Unable to get the VLookup property of the WorksheetFunction class | Excel Discussion (Misc queries) | |||
Unable to get the Vlookup property of the WorksheetFunction class | Excel Programming | |||
Unable to get Match property of worksheetfunction | Excel Programming | |||
Unable to get the Text property of the WorksheetFunction class | Excel Programming | |||
Unable to get the Vlookup Property of the WorkSheetFunction Class | Excel Programming |