compare row level array value vs. array aggregate value

[JosieSlift]

Hello! I am no VBA expert but was recently tasked with supporting and enhancing a project developed using VBA/Excel by another group within my company.

I am trying to take this basic logic:

For row = 1 To UBound(ARRAY)
VARIABLE = CCur(5 * _
ARRAY(row).Column1 + _
ARRAY(row).Column2 / _
ARRAY(row).Coulmn3)
Next row

and turn it into this:

For row = 1 To UBound(ARRAY)
VARIABLE = (CCur(5 * _
ARRAY(row).Column1 + _
ARRAY(row).Column2 / _
ARRAY(row).Coulmn3))

DIVIDED BY NUMBER OF ROWS IN THE ARRAY WITH THE SAME EndOfWeek VALUE

Next row

So I guess what I am asking is; how can I divide the result of a calculation at the finest grain row level of an array by a value which is derived by aggregating an entire column for the same array?

The greatest trouble I have had in trying to find an answer has so far been my inability to expertly describe the problem. Hopefully my description here makes sense.

Thank you very much!

 

[JosieSlift]

Skip! I just realized that the final array IS written to a sheet! I can try to do what you describe at this point!

I will play with the idea...

Thanks!

 

[Gavona]

I am not a programmer. This looks to me like a programmer's solution to something that can be done efficiently and effectively using a lot more in-built excel functionality and with VBA just to help it along and automate it. I think this is what SKIP was getting at.

I would create a range in excel containing the weekly totals (your "ArrayLevelCalculatedVariableValue"). It could well be that a pivottable would do this most simply and quickly.
I would use code to convert the pivot table to values, to fill the blanks .... and would end up with a single cell named range for each "ArrayLevelCalculatedVariableValue".

(RowLevelField1 + RowLevelField2 + RowLevelField3) / ArrayLevelCalculatedVariableValue may now be a simple(?) excel formula. Use the Indirect function to derive which "ArrayLevelCalculatedVariableValue" to use in each row.
In VBA:
Switch calculation to manual, add the formulae to the entire range (column) in one line of code, calculate just that range, convert to values, switch calculation back to automatic.

Regards,


Gavin

 

[Golom]

Arrays are probably not the model to use for this. Try a dictionary object

    Dim d                           As Object
    Set d = CreateObject("Scripting.Dictionary")
    For Row = 1 To UBound(xarray)
        rowkey = Format(xarray(Row).Column4,"yyyy-mm-dd")
        If d.Exists(rowkey) Then
            d.Item(rowkey) = d.Item(rowkey) + 1
        Else
            d.Add rowkey, 1
        End If
    Next Row

I just stuck "Column4" in as the name of the UDT field that contains your end of week value. Change as required.
Then your final computation just becomes

    Variable = Variable / _
        d.Item(Format(xarray(Row).Column4,"yyyy-mm-dd"))

 

[electricpete]

Dictionary object! I didn't even know there was such a thing. Cool.

 

[electricpete]

When I tried this line:
Set d = CreateObject(Scripting.Dictionary)
VBA highlighted "Scripting" and said "Compile Error - Variable not defined"

Is there something that needs to be set up or referenced for this type of code?

 

[electricpete]

Looking at help, the basic function of a dictionary object is similar to a dynamic array, except the indices can be anything we want (strings, integers, dates, etc).

 

[PHV]

Set d = CreateObject([!]"[/!]Scripting.Dictionary[!]"[/!])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[electricpete]

Yup. Forgot my quotes. Thanks PHV.

 

[electricpete]

Actually I had copied some code straight out of hte help:

Dim d                   'Create a variable
Set d = CreateObject(Scripting.Dictionary)
d.Add "a", "Athens"     'Add some keys and items
d.Add "b", "Belgrade"
d.Add "c", "Cairo"

I guess the help writers aren't familiar with the synatx?

 

[PHV]

http://msdn.microsoft.com/en-us/library/x4k5wbx4(VS.85).aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886