Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match
Hi. Can someone please pinpoint the error in this match function statement using a textbox which contains the lookup date to match a column of dates in column Z. Program works if I do not use the textbox otherwise it gives a runtime error: Set StartDate = Worksheets("Data1").Range("Z" & WorksheetFunction.Match(DateValue(TextBox1.Value), Worksheets("Data1").Range("Z3:Z600"), 0) + 2) StartDate declared as a range object. Dates are in 3/14/01 format. Textbox1 contains a date Column Z is a series of dates When I replace the textbox with a cell to contain the lookup date, the rest of the program works. So the error can be narrowed to this sub expression: DateValue(TextBox1.Value) Thanks for your advice. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match
Try Set StartDate = Worksheets("Data1").Range("Z" & WorksheetFunction.Match(DateValue(TextBox1), Worksheets("Data1").Range("Z3:Z600").Value, 0) + 2) If this post helps click Yes --------------- Jacob Skaria "John P" wrote: Hi. Can someone please pinpoint the error in this match function statement using a textbox which contains the lookup date to match a column of dates in column Z. Program works if I do not use the textbox otherwise it gives a runtime error: Set StartDate = Worksheets("Data1").Range("Z" & WorksheetFunction.Match(DateValue(TextBox1.Value), Worksheets("Data1").Range("Z3:Z600"), 0) + 2) StartDate declared as a range object. Dates are in 3/14/01 format. Textbox1 contains a date Column Z is a series of dates When I replace the textbox with a cell to contain the lookup date, the rest of the program works. So the error can be narrowed to this sub expression: DateValue(TextBox1.Value) Thanks for your advice. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match
Still won't work, Jacob "Jacob Skaria" wrote: Try Set StartDate = Worksheets("Data1").Range("Z" & WorksheetFunction.Match(DateValue(TextBox1), Worksheets("Data1").Range("Z3:Z600").Value, 0) + 2) If this post helps click Yes --------------- Jacob Skaria "John P" wrote: Hi. Can someone please pinpoint the error in this match function statement using a textbox which contains the lookup date to match a column of dates in column Z. Program works if I do not use the textbox otherwise it gives a runtime error: Set StartDate = Worksheets("Data1").Range("Z" & WorksheetFunction.Match(DateValue(TextBox1.Value), Worksheets("Data1").Range("Z3:Z600"), 0) + 2) StartDate declared as a range object. Dates are in 3/14/01 format. Textbox1 contains a date Column Z is a series of dates When I replace the textbox with a cell to contain the lookup date, the rest of the program works. So the error can be narrowed to this sub expression: DateValue(TextBox1.Value) Thanks for your advice. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match
Try
Dim dtTemp as Date dtTemp = CDate(TextBox1.Text) Set StartDate = Worksheets("Data1").Range("Z" & _ WorksheetFunction.Match(dtTemp, _ Worksheets("Data1").Range("Z3:Z600").Value, 0) + 2) OR try the below Dim dtTemp as Date dtTemp = CDate(TextBox1.Text) Msgbox WorksheetFunction.Match(dtTemp,Worksheets("Data1") .Range("Z3:Z600").Value, 0) If this post helps click Yes --------------- Jacob Skaria "John P" wrote: Still won't work, Jacob "Jacob Skaria" wrote: Try Set StartDate = Worksheets("Data1").Range("Z" & WorksheetFunction.Match(DateValue(TextBox1), Worksheets("Data1").Range("Z3:Z600").Value, 0) + 2) If this post helps click Yes --------------- Jacob Skaria "John P" wrote: Hi. Can someone please pinpoint the error in this match function statement using a textbox which contains the lookup date to match a column of dates in column Z. Program works if I do not use the textbox otherwise it gives a runtime error: Set StartDate = Worksheets("Data1").Range("Z" & WorksheetFunction.Match(DateValue(TextBox1.Value), Worksheets("Data1").Range("Z3:Z600"), 0) + 2) StartDate declared as a range object. Dates are in 3/14/01 format. Textbox1 contains a date Column Z is a series of dates When I replace the textbox with a cell to contain the lookup date, the rest of the program works. So the error can be narrowed to this sub expression: DateValue(TextBox1.Value) Thanks for your advice. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match
Using dtTemp = CDate(TextBox1.Text) still gives runtime error. So far, only using a cell to contain the lookup date makes the program work: Worksheets("Data1").Range("H2") = TextBox1.Value I am perplexed as to why this is so. "Jacob Skaria" wrote: Try Dim dtTemp as Date dtTemp = CDate(TextBox1.Text) Set StartDate = Worksheets("Data1").Range("Z" & _ WorksheetFunction.Match(dtTemp, _ Worksheets("Data1").Range("Z3:Z600").Value, 0) + 2) OR try the below Dim dtTemp as Date dtTemp = CDate(TextBox1.Text) Msgbox WorksheetFunction.Match(dtTemp,Worksheets("Data1") .Range("Z3:Z600").Value, 0) If this post helps click Yes --------------- Jacob Skaria "John P" wrote: Still won't work, Jacob "Jacob Skaria" wrote: Try Set StartDate = Worksheets("Data1").Range("Z" & WorksheetFunction.Match(DateValue(TextBox1), Worksheets("Data1").Range("Z3:Z600").Value, 0) + 2) If this post helps click Yes --------------- Jacob Skaria "John P" wrote: Hi. Can someone please pinpoint the error in this match function statement using a textbox which contains the lookup date to match a column of dates in column Z. Program works if I do not use the textbox otherwise it gives a runtime error: Set StartDate = Worksheets("Data1").Range("Z" & WorksheetFunction.Match(DateValue(TextBox1.Value), Worksheets("Data1").Range("Z3:Z600"), 0) + 2) StartDate declared as a range object. Dates are in 3/14/01 format. Textbox1 contains a date Column Z is a series of dates When I replace the textbox with a cell to contain the lookup date, the rest of the program works. So the error can be narrowed to this sub expression: DateValue(TextBox1.Value) Thanks for your advice. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match
If no match is found MATCH will return an error; (like in worksheet formula). To handle that. Dim dtTemp as Date Dim varTemp as variant Dim rngTemp as range dtTemp = CDate(TextBox1.Text) Set rngTemp=Worksheets("Data1").Range("Z3:Z600") varTemp=Application.match(dtTemp,rngTemp,0) If IsError(varTemp) then Msgbox "not found" Else Set StartDate = Worksheets("Data1").Range("Z" & varTemp + 2) End if If this post helps click Yes --------------- Jacob Skaria "John P" wrote: Using dtTemp = CDate(TextBox1.Text) still gives runtime error. So far, only using a cell to contain the lookup date makes the program work: Worksheets("Data1").Range("H2") = TextBox1.Value I am perplexed as to why this is so. "Jacob Skaria" wrote: Try Dim dtTemp as Date dtTemp = CDate(TextBox1.Text) Set StartDate = Worksheets("Data1").Range("Z" & _ WorksheetFunction.Match(dtTemp, _ Worksheets("Data1").Range("Z3:Z600").Value, 0) + 2) OR try the below Dim dtTemp as Date dtTemp = CDate(TextBox1.Text) Msgbox WorksheetFunction.Match(dtTemp,Worksheets("Data1") .Range("Z3:Z600").Value, 0) If this post helps click Yes --------------- Jacob Skaria "John P" wrote: Still won't work, Jacob "Jacob Skaria" wrote: Try Set StartDate = Worksheets("Data1").Range("Z" & WorksheetFunction.Match(DateValue(TextBox1), Worksheets("Data1").Range("Z3:Z600").Value, 0) + 2) If this post helps click Yes --------------- Jacob Skaria "John P" wrote: Hi. Can someone please pinpoint the error in this match function statement using a textbox which contains the lookup date to match a column of dates in column Z. Program works if I do not use the textbox otherwise it gives a runtime error: Set StartDate = Worksheets("Data1").Range("Z" & WorksheetFunction.Match(DateValue(TextBox1.Value), Worksheets("Data1").Range("Z3:Z600"), 0) + 2) StartDate declared as a range object. Dates are in 3/14/01 format. Textbox1 contains a date Column Z is a series of dates When I replace the textbox with a cell to contain the lookup date, the rest of the program works. So the error can be narrowed to this sub expression: DateValue(TextBox1.Value) Thanks for your advice. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheetfunction.match | Excel Programming | |||
Re : Excel VBA and WorkSheetFunction (Match) | Excel Programming | |||
Syntax for WorksheetFunction Match | Excel Programming | |||
worksheetfunction.match | Excel Programming | |||
Worksheetfunction MATCH | Excel Programming |