Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA: A problem with worksheetfunction MATCH
Hi
I need to find the first occurrence of a date in range on sheet in my procedure code. The range is on active sheet ("Data") of active workbook, and is defined as a dynamic range DataDate (which returns a range Data!$B$2:$B#). (Searched data is read from InputBox.) The code below returns an error: "Unable to get the Match property of the WorksheetFunction class" ...... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , [DataDate], 0) + 1 ...... The same error is returned, when I modify the code to ...... varDataDate = [DataDate] ...... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , varDataDate, 0) + 1 ...... Both [DataDate] and varDataDate are functional - in watch window: varDataDate - Type = Variant/Variant(1 to 4688, 1 to 1) [DataDate] - Type = Variant/Object/Range And the same error is returned with code: ...... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , ThisWorkbook.Sheets("Data").Range("B2:B" & ([DataRows]-1)), 0) + 1 ...... where the named range DataRows returns last used row on sheet Data. I'm trying to solve this from yesterday on - and no success so long. Maybe someone here can help! Thanks in advance! -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA: A problem with worksheetfunction MATCH
If you use .worksheetfunction, you're going to have to trap the error yourself.
dim VarRow1 as long on error resume next varrow1 = application.worksheetfunction.match(...) if err.number < 0 then 'not found err.clear end if on error goto 0 If you drop the .worksheetfunction, you can test for a returned error. dim VarRow1 as Variant 'could contain an error. varrow1 = application.match(...) if iserror(varrow1) then 'error was found else 'no error end if ========= But working with dates can be a problem. You could try something like this: dim VarRow1 as variant varRow1 = Application.Match(clng(CDate(varDate)), _ ThisWorkbook.Sheets("Data").Range("DataDate"), 0) if iserror(varrow1) then msgbox "no match! else varrow1 = varrow1 + 1 end if Sometimes treating the dates like just plain old numbers works nicely. Arvi Laanemets wrote: Hi I need to find the first occurrence of a date in range on sheet in my procedure code. The range is on active sheet ("Data") of active workbook, and is defined as a dynamic range DataDate (which returns a range Data!$B$2:$B#). (Searched data is read from InputBox.) The code below returns an error: "Unable to get the Match property of the WorksheetFunction class" ..... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , [DataDate], 0) + 1 ..... The same error is returned, when I modify the code to ..... varDataDate = [DataDate] ..... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , varDataDate, 0) + 1 ..... Both [DataDate] and varDataDate are functional - in watch window: varDataDate - Type = Variant/Variant(1 to 4688, 1 to 1) [DataDate] - Type = Variant/Object/Range And the same error is returned with code: ..... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , ThisWorkbook.Sheets("Data").Range("B2:B" & ([DataRows]-1)), 0) + 1 ..... where the named range DataRows returns last used row on sheet Data. I'm trying to solve this from yesterday on - and no success so long. Maybe someone here can help! Thanks in advance! -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA: A problem with worksheetfunction MATCH
Hi
I did get a solution from german NG meanwhile - in Match() I had to convert the date, I was searching for, to long integer. ...... varRow1 = Application.WorksheetFunction.Match(CLng(varDate), [DataDate], 0) + 1 -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Dave Peterson" wrote in message ... If you use .worksheetfunction, you're going to have to trap the error yourself. dim VarRow1 as long on error resume next varrow1 = application.worksheetfunction.match(...) if err.number < 0 then 'not found err.clear end if on error goto 0 If you drop the .worksheetfunction, you can test for a returned error. dim VarRow1 as Variant 'could contain an error. varrow1 = application.match(...) if iserror(varrow1) then 'error was found else 'no error end if ========= But working with dates can be a problem. You could try something like this: dim VarRow1 as variant varRow1 = Application.Match(clng(CDate(varDate)), _ ThisWorkbook.Sheets("Data").Range("DataDate"), 0) if iserror(varrow1) then msgbox "no match! else varrow1 = varrow1 + 1 end if Sometimes treating the dates like just plain old numbers works nicely. Arvi Laanemets wrote: Hi I need to find the first occurrence of a date in range on sheet in my procedure code. The range is on active sheet ("Data") of active workbook, and is defined as a dynamic range DataDate (which returns a range Data!$B$2:$B#). (Searched data is read from InputBox.) The code below returns an error: "Unable to get the Match property of the WorksheetFunction class" ..... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , [DataDate], 0) + 1 ..... The same error is returned, when I modify the code to ..... varDataDate = [DataDate] ..... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , varDataDate, 0) + 1 ..... Both [DataDate] and varDataDate are functional - in watch window: varDataDate - Type = Variant/Variant(1 to 4688, 1 to 1) [DataDate] - Type = Variant/Object/Range And the same error is returned with code: ..... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , ThisWorkbook.Sheets("Data").Range("B2:B" & ([DataRows]-1)), 0) + 1 ..... where the named range DataRows returns last used row on sheet Data. I'm trying to solve this from yesterday on - and no success so long. Maybe someone here can help! Thanks in advance! -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA: A problem with worksheetfunction MATCH
If that date isn't found, you may still have a problem.
I still like this: But working with dates can be a problem. You could try something like this: dim VarRow1 as variant varRow1 = Application.Match(clng(CDate(varDate)), _ ThisWorkbook.Sheets("Data").Range("DataDate"), 0) if iserror(varrow1) then msgbox "no match! else varrow1 = varrow1 + 1 end if And if you're going to post the same message to multiple newsgroups, please post once and include all the newsgroups in your header. Crossposting is fine. Multiposting means that one of us wasted his time. Thanks, Arvi Laanemets wrote: Hi I did get a solution from german NG meanwhile - in Match() I had to convert the date, I was searching for, to long integer. ..... varRow1 = Application.WorksheetFunction.Match(CLng(varDate), [DataDate], 0) + 1 -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Dave Peterson" wrote in message ... If you use .worksheetfunction, you're going to have to trap the error yourself. dim VarRow1 as long on error resume next varrow1 = application.worksheetfunction.match(...) if err.number < 0 then 'not found err.clear end if on error goto 0 If you drop the .worksheetfunction, you can test for a returned error. dim VarRow1 as Variant 'could contain an error. varrow1 = application.match(...) if iserror(varrow1) then 'error was found else 'no error end if ========= But working with dates can be a problem. You could try something like this: dim VarRow1 as variant varRow1 = Application.Match(clng(CDate(varDate)), _ ThisWorkbook.Sheets("Data").Range("DataDate"), 0) if iserror(varrow1) then msgbox "no match! else varrow1 = varrow1 + 1 end if Sometimes treating the dates like just plain old numbers works nicely. Arvi Laanemets wrote: Hi I need to find the first occurrence of a date in range on sheet in my procedure code. The range is on active sheet ("Data") of active workbook, and is defined as a dynamic range DataDate (which returns a range Data!$B$2:$B#). (Searched data is read from InputBox.) The code below returns an error: "Unable to get the Match property of the WorksheetFunction class" ..... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , [DataDate], 0) + 1 ..... The same error is returned, when I modify the code to ..... varDataDate = [DataDate] ..... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , varDataDate, 0) + 1 ..... Both [DataDate] and varDataDate are functional - in watch window: varDataDate - Type = Variant/Variant(1 to 4688, 1 to 1) [DataDate] - Type = Variant/Object/Range And the same error is returned with code: ..... varRow1 = Application.WorksheetFunction.Match(CDate(varDate) , ThisWorkbook.Sheets("Data").Range("B2:B" & ([DataRows]-1)), 0) + 1 ..... where the named range DataRows returns last used row on sheet Data. I'm trying to solve this from yesterday on - and no success so long. Maybe someone here can help! Thanks in advance! -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA: A problem with worksheetfunction MATCH
Hi
"Dave Peterson" wrote in message ... If that date isn't found, you may still have a problem. No problem in my procedure (at least this code row). This row is from delete routine which is a part of procedure. User is asked for source workbook, and then for date. When returned date is found in table, user is asked, want he/she overwrite the data. When yes, then the first (varRow1) and last (varRow2) row with this date are found (data are always sorted by date), and the range between is deleted. After that new data are read into table and table is sorted. And if you're going to post the same message to multiple newsgroups, please post once and include all the newsgroups in your header. Crossposting is fine. Multiposting means that one of us wasted his time. No multiposting of course. In other NG the posting was in german. :-) Arvi Laanemets |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA: A problem with worksheetfunction MATCH
Excellent point!
I'm not sure what the newsgroup etiquette is for different languages--maybe one post with the question in German followed by the translated question??? But my point about using application.worksheetfunction raising an error still needs addressing (I think). I didn't see anything in your original short snippet that would show that you're looking for that kind of error. Arvi Laanemets wrote: Hi "Dave Peterson" wrote in message ... If that date isn't found, you may still have a problem. No problem in my procedure (at least this code row). This row is from delete routine which is a part of procedure. User is asked for source workbook, and then for date. When returned date is found in table, user is asked, want he/she overwrite the data. When yes, then the first (varRow1) and last (varRow2) row with this date are found (data are always sorted by date), and the range between is deleted. After that new data are read into table and table is sorted. And if you're going to post the same message to multiple newsgroups, please post once and include all the newsgroups in your header. Crossposting is fine. Multiposting means that one of us wasted his time. No multiposting of course. In other NG the posting was in german. :-) Arvi Laanemets -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA: A problem with worksheetfunction MATCH
"Dave Peterson" wrote in message ... Excellent point! I'm not sure what the newsgroup etiquette is for different languages--maybe one post with the question in German followed by the translated question??? But my point about using application.worksheetfunction raising an error still needs addressing (I think). Look at my another posting from yesterday (Excel2000 VBA: How force the procedure to wait until queries are refreshed?). It's not the same procedure I was speaking about here, but another one from same application. It contains almost same code for deleting of rows. As you can see, the code row with application.worksheetfunction is processed only, when varDate is found in range DataDate. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) I didn't see anything in your original short snippet that would show that you're looking for that kind of error. Arvi Laanemets wrote: Hi "Dave Peterson" wrote in message ... If that date isn't found, you may still have a problem. No problem in my procedure (at least this code row). This row is from delete routine which is a part of procedure. User is asked for source workbook, and then for date. When returned date is found in table, user is asked, want he/she overwrite the data. When yes, then the first (varRow1) and last (varRow2) row with this date are found (data are always sorted by date), and the range between is deleted. After that new data are read into table and table is sorted. And if you're going to post the same message to multiple newsgroups, please post once and include all the newsgroups in your header. Crossposting is fine. Multiposting means that one of us wasted his time. No multiposting of course. In other NG the posting was in german. :-) Arvi Laanemets -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000 VBA: A problem with worksheetfunction MATCH
I didn't look at the other post and I didn't notice a connection.
Arvi Laanemets wrote: "Dave Peterson" wrote in message ... Excellent point! I'm not sure what the newsgroup etiquette is for different languages--maybe one post with the question in German followed by the translated question??? But my point about using application.worksheetfunction raising an error still needs addressing (I think). Look at my another posting from yesterday (Excel2000 VBA: How force the procedure to wait until queries are refreshed?). It's not the same procedure I was speaking about here, but another one from same application. It contains almost same code for deleting of rows. As you can see, the code row with application.worksheetfunction is processed only, when varDate is found in range DataDate. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) I didn't see anything in your original short snippet that would show that you're looking for that kind of error. Arvi Laanemets wrote: Hi "Dave Peterson" wrote in message ... If that date isn't found, you may still have a problem. No problem in my procedure (at least this code row). This row is from delete routine which is a part of procedure. User is asked for source workbook, and then for date. When returned date is found in table, user is asked, want he/she overwrite the data. When yes, then the first (varRow1) and last (varRow2) row with this date are found (data are always sorted by date), and the range between is deleted. After that new data are read into table and table is sorted. And if you're going to post the same message to multiple newsgroups, please post once and include all the newsgroups in your header. Crossposting is fine. Multiposting means that one of us wasted his time. No multiposting of course. In other NG the posting was in german. :-) Arvi Laanemets -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.WorksheetFunction.Match problem | Excel Worksheet Functions | |||
VBA WorksheetFunction problem | Excel Programming | |||
Syntax for WorksheetFunction Match | Excel Programming | |||
worksheetfunction.match | Excel Programming | |||
Worksheetfunction MATCH | Excel Programming |