I made that change which is how I got to the current problem of 999999 Integer should have been plenty if the usedrange was returning the actual used range.
On Sat, Apr 7, 2012 at 3:18 PM, Asa Rossoff <a...@lovetour.info> wrote: > integer type only goes to 32768 - use long > > -----Original Message----- > From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] > On Behalf Of dguillett1 > Sent: Saturday, April 07, 2012 2:53 PM > 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. > > Provide a file with examples > > > > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguille...@gmail.com > -----Original Message----- > From: Domain Admin > Sent: Saturday, April 07, 2012 3:42 PM > 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. > > I am not worried about the match function not finding a match, > assuming the match line of code is written correctly. It is > impossible to not have a match. > If the code is incorrect anything can happen. But forget the match > function for now. Do you have any idea why this code gives error 1004 > >>> Dim startrawdata As Integer >>> Dim stoprawdata As Integer >>> >>> contangoindex = 2 >>> stoprawdata = Sheets(RawData).UsedRange.Offset(-1).Rows.Count >>> THE ERROR IS ON THIS LINE > > On Sat, Apr 7, 2012 at 1:37 PM, Rajan_Verma <rajanverma1...@gmail.com> > wrote: >> 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 > > -- > 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
