On May 2, 12:04 pm, "Peter T" <peter_t@discussions> wrote:
> Hi Lars,
>
> It's difficult to follow quite what you are doing, following comments based
> merely on a glance of your functions.
>
> The most obvious possibility that would make your functions slow is multiple
> Union's, these become exponentially slower with many non contiguous areas,
> if that's what your filtered range gives. Your first function, I think,
> could be done in a single line -
>
> On error resume next
> Set SubRangeSingleCol = _
> QueryRange.Columns(Col).SpecialCells(xlCellTypeVisible)
>
> Not sure what's going overall with your second function and cell formula.
> But it might be quicker to do the Sum in the function rather than returning
> a range to the cell, to avoid the union.
>
> It's worth declaring your variables, eg Dim cl as Range. Also use a
> temporary range variable, when done assign to the function, eg -
> Dim rngOutput as
> ' build rngOutput
> Set SpecialLookUp = rng
>
> If you care to explain with references (names, address's etc) and simple
> data samples that can easily be recreated without seeing what you have,
> others may well be able to suggest more ideas and perhaps a different
> approach.
>
> Regards,
> Peter T
>
> "Law" <Lawrence.Colo...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > Hi All
>
> > I have 5 sheets. 4 of the sheets contain forecast data and 1 of them
> > contains Actual data.
>
> > The forecast sheets have the following formula under the required
> > columns at the bottom of the data range for calculating the
> > corresponding actual total when the forecast data is autofiltered:
>
> > '=SUM((SpecialLookUp(SubRangeSingleCol(QT_SAM_FCT4_1, 3), ACT!
> > QT_SAM_ACT_1, 3, 6)))
>
> > Essentially what I am doing here is retrieving the filtered row IDs in
> > the Forecast sheet and then looking up these rowIds in the Actual data
> > sheet and then summing these req. values of the Actual data in the
> > Forecast sheet.
>
> > The problem occurs when I filter the Actual data it takes ages for
> > Excel to re-calculate these lookup formulas in the Forecast sheets.
>
> > Any suggestion as to how to make the calculation quicker would be more
> > than welcome.
>
> > Function SubRangeSingleCol(QueryRange As Range, Col As Integer) As
> > Range
>
> > For Each cl In QueryRange.Columns(Col).Cells
>
> > If (cl.EntireRow.Hidden = False) Then
> > If SubRangeSingleCol Is Nothing Then
> > Set SubRangeSingleCol = cl
> > Else
> > Set SubRangeSingleCol = Union(SubRangeSingleCol, cl)
> > End If
> > End If
>
> > Next cl
>
> > End Function
>
> > Function SpecialLookUp(IDInputRange As Range, LookUpRange As Range,
> > IDCol As Integer, ResultCol As Integer) As Range
>
> > For Each cl In IDInputRange.Cells
> > For Each cl1 In LookUpRange.Columns(IDCol).Cells
> > If SpecialLookUp Is Nothing Then
> > If cl1.Value = cl.Value Then
> > Set SpecialLookUp = cl1.Offset(0, ResultCol)
> > End If
> > Else
> > If cl1.Value = cl.Value Then
> > Set SpecialLookUp = Union(SpecialLookUp,
> > cl1.Offset(0, ResultCol - IDCol))
> > End If
> > End If
> > Next cl1
>
> > Next cl
>
> > End Function
>
> > Regards
>
> > Lars- Hide quoted text -
>
> - Show quoted text -
Thanks Peter
Using a temp variable in the function made things a lot quicker.
Cheers
L
|
Similar Threads
