An older email I don't think you got a direct reply to... > With respect to your statement that the non intersect version below > will not always work. You said it is because usedrange does not > always start at A1 (it will for this case but still agree bad > practice to count on it that being the case.). > In your case it works because you are using usedrange for > the entire sheet and return wherever that intersects the data > column even if that column of data is not starting at the first row? > But my case if the data does not start at the first row may still > work but not be efficient because of a lot of wasted empty cells at > the top?
No, using an "absolute" column number (one that corresponds with the entire worksheet) to refer to a column relative to a specific range (the UsedRange in this instance) will plain select the wrong column unless the first column of the range happens to be the first column of the worksheet. You'd have the same issue addressing rows of a range using row numbers relative to the worksheet unless the range happens to start in the first row of the worksheet. This applies whether you use the Columns property of the range, the Rows property, the Cells property, the Range property, or the Offset property -- all of which are relative to the range applied to. The intersect method allows use of absolute worksheet references to select two ranges (in this case a column of the worksheet and the UsedRange of the worksheet), and then return a range where those two ranges overlap. You could use a really inefficient line of code to calculate the relative column/row/range from the absolute column/row/range you already know, and then use the Columns/Rows/Cells/Range/Offset property of the range to return the subrange of interest, but it just doesn't make sense to do so. It sounds like your UsedRange happens to start in A1, making absolute and relative row and column numbers the same, but I recommend learning to "do it right".. Should you deside to change your worksheet layout (or someone else does) and have, say, a blank column where column A is now, your code will select the wrong column by 1, and debugging to find out why might be difficult, since even when the variables specify the correct column numbers, the code will fail. > Set InputRange = > Application.Intersect(Sheets(ContangoSource).UsedRange, > Sheets(ContangoSource).Columns(ConDate)) > > with this > > Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate) Asa -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Domain Admin Sent: Monday, April 09, 2012 8:31 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Why is usedrange here returning the entire spreadsheet? All for the education. Keep it coming. Once I have things working I will go back and try to make the program more efficient and elegant. The statement below does not work if you remove the .value. Back to the not finding the match function error. >> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _ >> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0) With respect to your statement that the non intersect version below will not always work. You said it is because usedrange does not always start at A1 (it will for this case but still agree bad practice to count on it that being the case.). In your case it works because you are using usedrange for the entire sheet and return wherever that intersects the data column even if that column of data is not starting at the first row? But my case if the data does not start at the first row may still work but not be efficient because of a lot of wasted empty cells at the top? Set InputRange = Application.Intersect(Sheets(ContangoSource).UsedRange, Sheets(ContangoSource).Columns(ConDate)) with this Set InputRange = Sheets(ContangoSource).UsedRange.Columns(ConDate) ============================================================= On Mon, Apr 9, 2012 at 3:38 AM, Asa Rossoff <a...@lovetour.info> wrote: >> contangoindex = _ >> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _ >> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0) > RE: passing an array like this, without respect to using Intersect; > > Since WorksheetFunction.Match accepts an actual range even though Help > describes the argument as an array, it may well perform best if you just > pass the range reference (no .value at the end). That way it can decide how > to handle the data best for itself. The most important issue was to limit > the size of the range properly. > > -----Original Message----- > From: Asa Rossoff [mailto:a...@lovetour.info] > Sent: Monday, April 09, 2012 2:35 AM > To: 'excel-macros@googlegroups.com' > Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire > spreadsheet? > >> This works. Is there a reason why it is not better? > Yes. It won't always work. RANGE.Columns is relative to that range (as is > RANGE.Rows, RANGE.Cells, and RANGE.Range), not the worksheet. UsedRange > doesn't always start in A1. > >> This is puzzling. In the case of .rows(1) it can handle the range as >> the array but in this case you have to explicitly convert to an array. > Perhaps because in Excel 2007+ there are 16,384 columns, much less than the > million+ rows, > Your syntax might well work in prior versions of Excel, where there are just > 65,536 rows. > >> And then further down to this though not as easy to read and understand >> >> contangoindex = _ >> WorksheetFunction.Match(Sheets(RawData).Cells(2, BarDate).Value, _ >> Sheets(ContangoSource).UsedRange.Columns(ConDate).Value, 0) >> >> So what is happening here? Is this still just by adding the .value >> converting the range into an array as some secret VBA thing where you >> are extracting the values out of the range and they have to go >> somewhere to it creates and array for them? > Once you specify the .Value property of a range larger than one cell, you > are specifying an array. It doesn't matter if you don't create a variable > to refer to it. If you read the Help entry for WorksheetFunction.Match > you'll see that the data type for all the arguments is Variant. Your array > will be stored in memory as an array of Variant (the .Value property is > Variant) and stored inside another Variant (full name "Variant Array of > Variant"), and a pointer to it will be passed to WorksheetFunction.Match. > When Match is done with it's work, the array will be destroyed from memory > since there are no other pointers to it. If you had a need to refer to it > again, it would be a very good idea to create the variable first and not > have VBA waste energy creating and destroying the whole array multiple > times. > >> You are right though. Once you find a working bone you can keep >> gnawing on it until down to minimal shard that still functions. > It can help in understanding what's what. > >> When you use SET to create pointers they can only be done inside a >> Sub. If you want to use them in another SUB do you have to do the SET >> again or if in the same module will the SET be know by other Subs? > It's not the Set statement that matters for your question. All variables, > as well as procedures (be they Sub or Function procedures), and modules, > have a certain scope within which they can be referred to. What determines > the scope, in the case of a variable, is where it is declared, what > statement was used to declare it, and whether it is passed ByRef to any > other Sub or Function (although in the last case the other procedure would > have it's own local name for the variable, even though it points to the same > memory location). > > Variables can be declared at the module level as: > Public varname As vartype ' uses global or optionally workbook scope > Private varname As vartype ' uses module scope > Dim varname As vartype ' uses module scope > > In a procedure they can be declared: > Dim varname As vartype ' uses local (procedure) scope > Static varname as vartype ' uses local (procedure) scope and retains value > between calls > > Then use Set if you are assigning an object to a variable, or use Let or the > usual shorthand of varname=value if you are assigning any other data type. > > Asa -- 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
