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
