Ranges and arrays are handled distinctly from each other, although often you can get away with the same syntax in VBA. I'd have to look for examples for you, not sure. Try search terms. If you search for code online, you might find the wealth of VB6 code online useful (although not so much in dealing with Excel-specific objects). It's the version of Visual Basic that VBA is based on, and they are in many respects the same language.
Did you see the recent posts in the group here with links to Excel/VBA reference sites? Asa -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Saturday, April 07, 2012 8:57 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? I tried what you said. Is it trying to make an array out of a range, or just restrict the range (looks more like the latter). Still got the error Unable to get the match property of the worksheetfunction class. I have verified all the individual components have correct values so still seems like some issue with the object hierarchy. I have a solution now so really do not need an answer to this problem though I would like to understand why it does not work. I take it excel or VBA does not treat a range as an array internally though it logically could? I am not resisting using ranges because of lack of understanding though they certainly are not second nature. I just think since virtually all the work that will be done is running loops through the columns it is more efficient to use the column names as indexes and do the work with cells(row, col). I of course may be mistaken. Where is the best repository of examples of well documented code of hopefully more than trivial out of book snippets? I learn best by looking at lots of examples. On Sat, Apr 7, 2012 at 8:35 PM, Asa Rossoff <a...@lovetour.info> wrote: > I just checked the help for WorksheetFunction.Match; the second argument is > specified as an array, not a range. > > You may be running into an out of memory condition since Excel is probably > copying the range to an array in memory. Even if Excel is smart enough to > limit the array size to UsedRange automatically (not sure), your UsedRange > was too large before, not sure if you got that taken care of. > > Out of curiousity, try > contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2, > BarDate).Value, > Application.Intersect(Sheets(ContangoSource).UsedRange,Sheets(ContangoSource > ).Columns(ConDate)), 0) > > If that works, it emphasizes the wisdom in restricting your ranges to begin > with (again, easier if you use range objects from the start rather than just > storing indexes). I know, you'd have to wrap your head around the different > coding style that would be requires to refer to everything, but help is > available. > > Asa > > -----Original Message----- > From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] > On Behalf Of Domain Admin > Sent: Saturday, April 07, 2012 8:15 PM > To: excel-macros@googlegroups.com > Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire > spreadsheet? > > Thanks for that. I will check it all out. I did create my own > equivalent and it works > > contangoindex = 1 > Do While Sheets(RawData).Cells(2, BarDate).Value <> > Sheets(ContangoSource).Cells(contangoindex, ConDate).Value > contangoindex = contangoindex + 1 > Loop > > > > On Sat, Apr 7, 2012 at 8:08 PM, Asa Rossoff <a...@lovetour.info> wrote: >> Hi Howard, >> With objectname >> Only takes effect in specific instances of using a leading ".". Nothing >> else in the with block is effected. It's an "anonymous" (no variable > name) >> equivalent to: >> Set MyObject = objectname >> 'and then placing MyObject before each of those naked periods you >> would have used in the With block. >> >> >> Some tips on debugging can be found at these links: >> Some basics: >> > http://www.computing.net/howtos/show/debugging-vba-code-101-a-tutorial/720.h >> tml >> http://www.ozgrid.com/Excel/free-training/ExcelVBA1/excelvba1lesson17.htm >> me answering some debugging questions: >> http://chandoo.org/forums/topic/debugging-custom-vba-function >> Mainly read the "Debugger" section and the sections that follow here: >> http://msdn.microsoft.com/en-us/library/ee358847.aspx >> Read the "Debug Menu" topics in Help >> >> >>> contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2, >>> BarDate).Value, Sheets(ContangoSource).Columns(ConDate), 0) >>> >>> and the excel equivalent to this just from the spreadsheet >>> =MATCH(A2,ContangoSource!A:A) >>> returns the correct value >> >> When code execution stops at the problem line, analyze each parameter >> carefully. >> E.g.: What is the value of BarDate at that point? It might not be what > you >> expect, and the fault in your code could be elsewhere. For it to match > the >> function you described using on in a sheet cell, Bardate would have to > refer >> to column A, so it would have to be the number 1. If it's not, that's a >> problem >> >> Examine each component of the failing statement. To do so from the > Immediate >> pane, you might try to answer the following questions (you can also use > the >> Locals and Watch panes, among other methods): >> Is BarDate 1 ? >> Is RawData set to the name of the RawData sheet? >> Is TypeName(Sheets(RawData)) "Worksheet" ? >> or >> Is Sheets(RawData).Name = RawData ? (actually it either will be or the >> object will not be valid at all) >> Is TypeName(Sheets(RawData).Cells(2, BarDate)) "Range"? >> or >> Is Sheets(RawData).Cells(2, BarDate).Address "$A$2"? >> Is Sheets(RawData).Cells(2, BarDate).Value what you expect? >> etc. >> >> These are the questions you ask once you double check the basic syntax of > a >> line. Break down all your assumptions. You will most likely find > something >> is not what you expect. When you do, backtrack to see how that happened. >> One way besides a syntactical bug in your code is that the name of a sheet >> isn't exactly what you think it is, or the value of a cell isn't exactly >> what you think it is (or even what it looks to be). There is the > occasional >> VBA bug, too, that sometimes require workarounds, but that is the rare >> exception. >> >> Asa >> >> -----Original Message----- >> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] >> On Behalf Of Domain Admin >> Sent: Saturday, April 07, 2012 6:29 PM >> To: excel-macros@googlegroups.com >> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire >> spreadsheet? >> >> I notice in all the other match functions they are inside a >> With Sheets(somesheet) >> >> so I tried putting sheets(rawdata).worksheetfunction ... >> but still failed though I did not expect it to work since the match >> function is trying to work across sheets. I hoped it might since >> maybe it just needed some way to find the match function. Is this >> whole concept of the single match function flawed even though I >> could make the equivalent excel function work? I had the match >> reversed. Code should be >> >> startrawdata = 2 >> stoprawdata = Sheets(RawData).UsedRange.Rows.Count 'end of the >> RawData columns >> ' Determine where the index in ContangSource is of the first date >> that matches the first date in RawData >> contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2, >> BarDate).Value, Sheets(ContangoSource).Columns(ConDate), 0) >> >> and the excel equivalent to this just from the spreadsheet >> =MATCH(A2,ContangoSource!A:A) >> returns the correct value >> >> >> On Sat, Apr 7, 2012 at 5:50 PM, Domain Admin <domainqu...@gmail.com> > wrote: >>> Well the usedrange problem is solved. I printed out just the cell >>> count of the used range and it indicated there was a column with data >>> somehow even though invisible. >>> So I deleted the column and that reduced the number. So I thought >>> there must still somehow be data in the "empty" rows so I did another >>> delete the same as before but now the number of rows is correct. >>> Scary since I do not know why the second time deleted it or what was >>> there before. I will need to put in some check for invalid data past >>> the end of the valid data though I think all that would happen is it >>> would run real slow. >>> >>> For this statement >>> >>> startrawdata = >>> WorksheetFunction.Match(Sheets(ContangoSource).Cells(2, >>> ConDate).Value, Sheets(RawData).Columns(BarDate), 0) >>> >>> I determined Sheets(RawData).Columns(BarDate) is returning the correct >>> entire column as the range. I also determined >>> Sheets(ContangoSource).Cells(2, ConDate).Value is returning the >>> expected value so the error message of >>> Unable to find the match property of the worksheetfunction class >>> seems to say some misuse of the object hierarchy to me but I do not >>> see it. I can make what I think is the equivalent pure excel >>> function return the correct answer just plugging it into the >>> spreadsheet. >>> >>> On Sat, Apr 7, 2012 at 5:17 PM, Domain Admin <domainqu...@gmail.com> >> wrote: >>>> I read that after I posted. But doesn't that mean >>>> >>>> Sheets(Results).Cells.Resize(-1).Offset(1).Delete >>>> >>>> that you suggested would be deleting ever row in the sheet except the >>>> header and not just the used range? >>>> >>>> In any case I am still screwed on this returning 1000000 >>>> stoprawdata = Sheets(RawData).UsedRange.Rows.Count >>>> >>>> and this not working at all >>>> >>>> startrawdata = >>>> WorksheetFunction.Match(Sheets(ContangoSource).Cells(2, >>>> ConDate).Value, Sheets(RawData).Columns(BarDate), 0) >>>> >>>> If you want to keep helping I am glad to have it because I am >>>> basically trying pseudo random things to debug and so far have only >>>> solved one of the 3 problems today myself. >>>> >>>> On Sat, Apr 7, 2012 at 5:09 PM, Asa Rossoff <a...@lovetour.info> wrote: >>>>> On Worksheet.Cells, Help says: "Returns a Range object that represents >> all >>>>> the cells on the worksheet (not just the cells that are currently in >> use." >>>>> >>>>> For info on range references check "How to: Reference Cells and Ranges" >> in >>>>> VBA Help. >>>>> However, basically in this case what you are noticing is the notation >> for >>>>> entire rows and columns. >>>>> A large range of cells: A1:Z1048576 >>>>> A range of entire columns: A:Z >>>>> A range of entire rows: 1:1048576 >>>>> >>>>> The latter naturally, with the definition of .Cells at hand, is the >> entire >>>>> worksheet. It could also be described in terms of columns: A:XFD >>>>> Or in terms of cells: A1:XFD1048576 >>>>> >>>>> Asa >>>>> >>>>> -----Original Message----- >>>>> From: excel-macros@googlegroups.com >> [mailto:excel-macros@googlegroups.com] >>>>> On Behalf Of Domain Admin >>>>> Sent: Saturday, April 07, 2012 5:00 PM >>>>> To: excel-macros@googlegroups.com >>>>> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the > entire >>>>> spreadsheet? >>>>> >>>>> using Debug.Print ActiveSheet.Cells.Address returns $1:$1048576 >>>>> which makes me think is is the true number of rows but >>>>> would seem to address only 1 column so I guess I am reading it wrong? >>>>> I thought cells and usedrange were pretty much equivalent but >>>>> apparently not. >>>>> >>>>> On Sat, Apr 7, 2012 at 4:51 PM, Domain Admin <domainqu...@gmail.com> >> wrote: >>>>>> Writing that book should be easy. Just collect all your question and >>>>>> answer thread and put them in a book. >>>>>> >>>>>> I do have Option Explicit >>>>>> >>>>>> The current usedrange does seem to return the correct range looking at >>>>>> the address so >>>>>> that clearcontents should work but the resize concept seems good. >>>>>> >>>>>> I read somewhere to use double for the indexes but that is just a >>>>>> larger long so either way >>>>>> I should be covered. Using double now may just waste a few bytes? >>>>>> >>>>>> Debug.Print Sheets(RawData).UsedRange.Address returns $A$1:$I$1000000 >>>>>> but doing it on the results page returns correct range >>>>>> >>>>>> Trying your change match statement >>>>>> startrawdata = WorksheetFunction.Match(Sheets(ContangoSource).Cells(2, >>>>>> ConDate).Value, Sheets(RawData).Columns(BarDate), 0) >>>>>> >>>>>> gave Unable to get the match property of the worksheetfunction class >>>>>> >>>>>> On Sat, Apr 7, 2012 at 4:19 PM, Asa Rossoff <a...@lovetour.info> wrote: >>>>>>> The term reference in Excel, when referring to a cell or range, means >> the >>>>>>> textual description of that cell or range (the Address property of a >>>>> range >>>>>>> object returns the range reference). In VBA and other object > oriented >>>>>>> languages, reference also refers to variables that refer to an > object, >>>>> but >>>>>>> that's a whole nother topic. In any case, the word has a special >>>>> meaning, >>>>>>> so I'm glad you posted more of your code to clarify what you meant :) >>>>>>> >>>>>>> (1) Make sure OPTION EXPLICIT is the first line in your module, it >> will >>>>>>> alert you if VBA thinks you didn't declare a variable (i.e. you >> misspell >>>>> a >>>>>>> variable name or other keyword somewhere) >>>>>>> (2) In InitRefs() I would use (to ensure UsedRange will be updated): >>>>>>> Sheets(Results).UsedRange.ENTIREROW.Offset(1).DELETE ' delete >> results >>>>>>> from previous run >>>>>>> OR, likely a hair faster: >>>>>>> Sheets(Results).Cells.Resize(-1).Offset(1).Delete ' delete results >>>>> from >>>>>>> previous run >>>>>>> (3) All variables storing row or column indexes should be Long. In >>>>>>> LoadContangoSource() try using these declares: >>>>>>> dim contangoindex As Long >>>>>>> Dim startrawdata As Long >>>>>>> Dim stoprawdata As Long >>>>>>> Dim index As Long >>>>>>> In your global declares: >>>>>>> Public BarDate As Long >>>>>>> Public ConDate As Long >>>>>>> Public Contango As Long >>>>>>> Public EContango As Long '(I assume this is a row/column index) >>>>>>> (4) In Your problem line, you need to specifiy a range object to > match >>>>> in. >>>>>>> You are trying to use a variable holding an integer value as a >> property >>>>> of a >>>>>>> worksheet, and also expecting it to return a range object when you do >> so. >>>>>>> This should work, though it will search the header row and > potentially >>>>> the >>>>>>> unused range (a reason to use range variables and define the exact >> range >>>>>>> needed in advance since it is time consuming to create range objects >>>>>>> repeatedly): >>>>>>> startrawdata = >>>>>>> WorksheetFunction.Match(Sheets(ContangoSource).Cells(2, >>>>>>> ConDate).Value, Sheets(RawData).Columns(BarDate), 0) >>>>>>> (5) In your rawdata loop, there is no need for parenthesis around the >>>>>>> boolean expression. VBA will always evaluate the entire expression >>>>> between >>>>>>> If/Then separately from the rest of the If/Then statement. I haven't >>>>>>> analyzed your loop too closely, but you can consider if you could use >>>>>>> Range.Find or WorksheetFunction.Match instead of looping through the >>>>> whole >>>>>>> range. If that would result in fewer iterations it could be >>>>> significantly >>>>>>> faster. >>>>>>> >>>>>>> Asa >>>>>>> >>>>>>> >>>>>>> -----Original Message----- >>>>>>> From: excel-macros@googlegroups.com >>>>> [mailto:excel-macros@googlegroups.com] >>>>>>> On Behalf Of Domain Admin >>>>>>> Sent: Saturday, April 07, 2012 3:32 PM >>>>>>> To: excel-macros@googlegroups.com >>>>>>> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the >> entire >>>>>>> spreadsheet? >>>>>>> >>>>>>> No not a range object, just a reference. But here is the entire >>>>>>> program. I removed the currently unused portions and ignore Public >>>>>>> just have not bothered to change that yet. >>>>>>> >>>>>>> ' Column reference objects >>>>>>> Public BarDate As Double >>>>>>> >>>>>>> Public ConDate As Double >>>>>>> Public Contango As Long >>>>>>> Public EContango As Double 'the expanded set of contango values >>>>>>> to cover all time periods >>>>>>> >>>>>>> ' Sheet names and pointers >>>>>>> Const RawData As String = "RawData" >>>>>>> Const Results As String = "Results" >>>>>>> Const ContangoSource As String = "ContangoSource" >>>>>>> >>>>>>> >>>>>>> >>>>>>> Sub InitRefs() >>>>>>> ' >>>>>>> ' Sub which sets the data column names and misc values for the > RawData >>>>> sheet >>>>>>> ' >>>>>>> With Sheets(RawData) >>>>>>> BarDate = WorksheetFunction.Match("BarDate", .Rows(1), 0) >>>>>>> End With >>>>>>> With Sheets(ContangoSource) >>>>>>> ConDate = WorksheetFunction.Match("ConDate", .Rows(1), 0) >>>>>>> Contango = WorksheetFunction.Match("Contango", .Rows(1), 0) >>>>>>> End With >>>>>>> >>>>>>> Sheets(Results).UsedRange.Offset(1).ClearContents ' clear out >>>>>>> results from previous run >>>>>>> >>>>>>> End Sub >>>>>>> Sub LoadContangoSource() >>>>>>> >>>>>>> ' Sub which takes the data from the ContangoSource sheet which is >> per >>>>> day >>>>>>> and >>>>>>> ' copies it to the RawData sheet with entries for each of the bars. >>>>>>> >>>>>>> Dim contangoindex As Integer >>>>>>> Dim startrawdata As Double >>>>>>> Dim stoprawdata As Double >>>>>>> Dim index As Integer >>>>>>> >>>>>>> contangoindex = 2 >>>>>>> stoprawdata = Sheets(RawData).UsedRange.Rows.Count 'end of the >>>>>>> RawData columns THE LINE THAT RETURNS 999999 >>>>>>> ' 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, 0) THE FAILING MATCH >>>>>>> >>>>>>> ' Now loop through the RawData entries copying the contango value > as >>>>>>> many times as needed for the matching dates in RawData. >>>>>>> ' Since RawData can be from charts with any interval there may be >>>>>>> many entries for each matching ContangoSource date. >>>>>>> For index = startrawdata To stoprawdata >>>>>>> If (Sheets(RawData).Cells(index, BarDate).Value = >>>>>>> Sheets(ContangoSource).Cells(contangoindex, ConDate).Value) Then >>>>>>> Sheets(RawData).Cells(index, EContango).Value = >>>>>>> Sheets(ContangoSource).Cells(contangoindex, Contango).Value >>>>>>> Else >>>>>>> contangoindex = contangoindex + 1 >>>>>>> End If >>>>>>> Next index >>>>>>> >>>>>>> End Sub >>>>>>> >>>>>>> On Sat, Apr 7, 2012 at 3:22 PM, Asa Rossoff <a...@lovetour.info> > wrote: >>>>>>>> "bardate is a column" do you mean it's a range object? >>>>>>>> then instead of >>>>>>>> Sheets(RawData).BarDate >>>>>>>> just use: >>>>>>>> BarDate >>>>>>>> >>>>>>>> For more help pleas post whole procedure >>>>>>>> >>>>>>>> -----Original Message----- >>>>>>>> From: excel-macros@googlegroups.com >>>>> [mailto:excel-macros@googlegroups.com] >>>>>>>> On Behalf Of Domain Admin >>>>>>>> Sent: Saturday, April 07, 2012 3:14 PM >>>>>>>> To: excel-macros@googlegroups.com >>>>>>>> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the >> entire >>>>>>>> spreadsheet? >>>>>>>> >>>>>>>> I figured the extra row was cleared but that is not a problem. >>>>>>>> It seems on testing that clearcontents is working fine leaving the >>>>>>>> formats alone, It appears that delete is also leaving the formats >>>>>>>> alone. But this still leaves me with 2 problems >>>>>>>> >>>>>>>> 1. why does usedrange seem to be returning the entire row count for >>>>>>>> the spreadsheet? >>>>>>>> 2. Why does this statement return Object does not support this >>>>>>>> property or method >>>>>>>> >>>>>>>> startrawdata = >>>>>>>> WorksheetFunction.Match(Sheets(ContangoSource).Cells(2, >>>>>>>> ConDate).Value, Sheets(RawData).BarDate, 0) >>>>>>>> where Condate is a column and BarDate is a column and hopefully >>>>>>>> Sheets(RawData).BarDate returns that column as the range for the >>>>>>>> Match. >>>>>>>> >>>>>>>> >>>>>>>> On Sat, Apr 7, 2012 at 2:53 PM, Asa Rossoff <a...@lovetour.info> >> wrote: >>>>>>>>> not well documented but entire row and column formats are only >> stored >>>>>>> once >>>>>>>>> in your file and do not effect usedrange. >>>>>>>>> Yeah, your efficient statement will clear the used range of the >>>>>>>> spreadsheet >>>>>>>>> except for the first row. it will also clear the next row down > from >>>>> the >>>>>>>>> used range (which was already clear). Not to mention, even if you >> had >>>>>>>>> specified the correct range, ClearContents is like the delete key - >> it >>>>>>>>> clears the text and formulas. Clearing is not the same as > deleting. >>>>>>>>> >>>>>>>>> If you have a table of data that is the only significant data on > the >>>>>>>> sheet, >>>>>>>>> this will delete other rows/columns, I beliewve - untested - save >> first >>>>>>>>> (although, syou probably don't need to code this; this situation >> does >>>>> not >>>>>>>>> arise if you delete rows that are no longer needed as a matter of >> habit >>>>>>>>> rather than clearing them): >>>>>>>>> with activesheet >>>>>>>>> set r=.range("a1").currentregion >>>>>>>>> .Cells.Resize(-r.rows.count).offset(r.rows.count).delete >>>>>>>>> .cells.resize(0,-r.columns.count).offset(r.columns.count).delete >>>>>>>>> end with >>>>>>>>> >>>>>>>>> Asa >>>>>>>>> >>>>>>>>> -----Original Message----- >>>>>>>>> From: excel-macros@googlegroups.com >>>>>>> [mailto:excel-macros@googlegroups.com] >>>>>>>>> On Behalf Of Domain Admin >>>>>>>>> Sent: Saturday, April 07, 2012 2:40 PM >>>>>>>>> To: excel-macros@googlegroups.com >>>>>>>>> Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the >>>>> entire >>>>>>>>> spreadsheet? >>>>>>>>> >>>>>>>>> Ok then I can not use this method at all becaues the columns have >>>>>>>>> formats set for the entire column (really not efficient for that to >> be >>>>>>>>> part of userange). >>>>>>>>> And I am guessing this statement that I thought was efficient is >>>>>>>>> actually clearing the entire spreadsheet and not jus the part with >>>>>>>>> data (except the header row)? >>>>>>>>> >>>>>>>>> Sheets(Results).UsedRange.Offset(1).ClearContents >>>>>>>>> >>>>>>>>> Best alternative for finding the last row of data in a spreadsheet? >>>>>>>>> >>>>>>>>> And this statement is returning Object does not support this >> property >>>>>>>>> or method. I was pretty sure it would not work but worth a shot. >>>>>>>>> >>>>>>>>> startrawdata = >>>>>>>>> WorksheetFunction.Match(Sheets(ContangoSource).Cells(2, >>>>>>>>> ConDate).Value, Sheets(RawData).BarDate, 0) >>>>>>>>> >>>>>>>>> is the problem that worksheetfunction needs a particular sheet? >>>>>>>>> ConDate and BarDate are column names and this concept seems to work >>>>>>>>> elsewhere >>>>>>>>> but not sure if Sheets(RawData).Bardate really returns that column >> as a >>>>>>>>> range? >>>>>>>>> >>>>>>>>> On Sat, Apr 7, 2012 at 2:30 PM, Asa Rossoff <a...@lovetour.info> >> wrote: >>>>>>>>>> you should highlight the entire rows (and same for extra columns), >>>>>>>>>> right-click, delete >>>>>>>>>> >>>>>>>>>> the delete key won't cut it (clears the text and formulas but >> leaves >>>>>>>>>> formats) >>>>>>>>>> >>>>>>>>>> or I select those rows/columns, then Alt-E,D >>>>>>>>>> >>>>>>>>>> if selecting some cells but not entire row/column, you can delete >>>>> entire >>>>>>>>> row >>>>>>>>>> with Alt-E,D,R and entire column with Alt-E,D,C >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> From: excel-macros@googlegroups.com >>>>>>>> [mailto:excel-macros@googlegroups.com] >>>>>>>>>> On Behalf Of tangledweb >>>>>>>>>> Sent: Saturday, April 07, 2012 2:25 PM >>>>>>>>>> To: excel-macros@googlegroups.com >>>>>>>>>> Subject: $$Excel-Macros$$ Why is usedrange here returning the >> entire >>>>>>>>>> spreadsheet? >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> This statement is returning 999999 though I deleted everthing >> below >>>>> the >>>>>>>>>> actual data to make sure no cells below it were used >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> stoprawdata = Sheets(RawData).UsedRange.Rows.Count - 1 >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> 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 >>>>> >>>>> -- >>>>> 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
