In this case match function will return a result if it value match in range, but it will not return an error if values does not match in Range.. On Error is just to escape from error..
Rajan. -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Apr/Sun/2012 02:03 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ runtime error on assignment trying to find the end of a column. Seems like should be simple. In this case it will be impossible for the match function to not find a match but thanks. No idea on why the assignment to stoprawdata fails? On Sat, Apr 7, 2012 at 1:17 PM, Rajan_Verma <rajanverma1...@gmail.com> wrote: > If match function dost not find any value it return an error , to > escape this error use the following code > > > > On Error Resume Next > > WorksheetFunction.Match(Sheets(ContangoSource).Cells(2, > ConDate).Value, > Sheets(RawData).BarDate) > > On Error Goto 0 > > > > > > Rajan. > > From: excel-macros@googlegroups.com > [mailto:excel-macros@googlegroups.com] > On Behalf Of tangledweb > Sent: Apr/Sun/2012 12:43 > To: excel-macros@googlegroups.com > Subject: $$Excel-Macros$$ runtime error on assignment trying to find > the end of a column. Seems like should be simple. > > > > Sigh, the things I know I don't know are increasing faster than the > things I know. > > > > I get a 1004 error on the assignment to stoprowdata below. > Sheets(RawData) has been used successfully before so I do not think > that is wrong. So I assume the problem is > > in the rest of the statement. But if so I would have thought the > compile would fail but it did not. Along with the solution to this > if I get one I would appreciate getting any tips on > > how to debug this as the error statement and help on it was not > informative to me at least and not sure how I could break this down > into smaller pieces to see what is failing. > > > > > > Dim startrawdata As Integer > Dim stoprawdata As Integer > > contangoindex = 2 > stoprawdata = Sheets(RawData).UsedRange.Offset(-1).Rows.Count > 'end of the RawData columns > > > > Also since the above statement did not work I assume if it gets to the > statement below that will fail too. ConDate and BarDate are column > header references > > > > ' Determine where the index in ContangSource is of the first date > that matches the first date in RawData > startrawdata = > WorksheetFunction.Match(Sheets(ContangoSource).Cells(2, > ConDate).Value, Sheets(RawData).BarDate) > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like > Please Help, Urgent, Need Help, Formula Problem, Code Problem, and > Need Advice will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum > in signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. > Forum owners and members are not responsible for any loss. > > ---------------------------------------------------------------------- > -------------------------------- To post to this group, send email to > excel-macros@googlegroups.com > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like > Please Help, Urgent, Need Help, Formula Problem, Code Problem, and > Need Advice will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum > in signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. > Forum owners and members are not responsible for any loss. > > ---------------------------------------------------------------------- > -------------------------------- To post to this group, send email to > excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com