Another one I started typing didn't finish and send.
> (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.
Another speed-up trick for when you do need to loop through a range, is to
copy the range to a variant array first, then loop through the array
instead:
Dim CellsOfInterest As Variant
Dim InputRange As Range
Set InputRange = Range(whatever)
' Next line creates an array of Variant that could include String, Double,
Currency, Date/Time, Boolean, or Variant/Empty. Since values are converted
to VBA datatypes, minor rounding and extra time are involved:
CellsOfInterest = InputRange.Value ' Creates an array
' This ALTERNATIVE line is similar, but faster, and returns values as
internally stored on a worksheet. Type will only be String, Double, or
Variant/Empty:
CellsOfInterest = InputRange.Value2 ' Creates an array
This array you've created has two dimension. The first is for the row, the
second, the column.
'If you don't know how many rows and columns you've retrieved, you can check
the size of the array:
Dim RowCount As Long, ColumnCount As Long
RowCount=UBound(CellsOfInterest, 1)
ColumnCount=UBound(CellsOfInterest, 2)
'Then for example, loop Through rows, check certain columns for conditions,
and if met, copy entire row to another array, manipulate in place, etc.:
Dim Row As Long, Column As Long
Dim
For Row = 1 To RowCount
For Column = 1 To ColumnCount
'''''''''''''''''''''' no time to give you full examples now.. you may not
even be interested :)
Quickly, though, I'll point out that if you have copied desired values to a
new two-dimensional variant array, it could be copied to the destination.
Dim OutputArray As Variant
Dim OutputRowCount As Long, OutputColumnCount As Long
Dim OutputRange As Range
' When OutputArray is ready, and OutputRowCoutn/OutputColumnCount set during
population of that array...
Set OutputRange=Sheets("sheetname").range("a1").resize(OutputRowCount,
OutputColumnCount)
OutputRange.Value = OutputArray
Off for the evening,
Take it easy,
Asa
-----Original Message-----
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Asa Rossoff
Sent: Saturday, April 07, 2012 4:19 PM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Why is usedrange here returning the entire
spreadsheet?
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
