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!

 

[SkipVought]

Hi,

Are you referring to a RANGE on a SHEET, since you refer to ROWS and COLUMNS?

How are you defining ARRAY, which, BTW, is a VBA Reserve Word (Not an Excel Object)?

How are you defining Columnn?

How are you defining EndOfWeek?

Please be CLEAR, CONCISE and COMPLETE.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[electricpete]

I would assume you are working with an array, since ubound wouldn't work with a range.

I would also assume you have renamed the name of the array from some valid name to "array". Array is not a valid name for an array or any other variable... it is a reserved word as identified above.

And by the way, where are the endofweek values stored? Another field of your database... for example MYARRAY(row).Column4 ... MYARRAY(row).endofweek ?

What are the permissible values for endofweek ?

I am guessing you need another loop through your data before you get to your code above to count the number of rows containing each value of endofweek and store them in counter variables for each value of endofweek.

 

[mintjulep]

Is EndOfWeek one of the columns in the array?

You'd need to loop through and count them.

Maintaining your (interesting) syntax, and imagining that the endofweek is stored in column4:

For row = 1 To UBound(ARRAY)

If ARRAY(row).column4 = EndOfWeek then Count = Count + 1

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

ANSWER = VARIABLE / Count

Next row

By the way, you understand that your logic will result in the value of VARIABLE being based solely on whatever is in the LAST row of the array, right?

 

[JosieSlift]

Hello thanks for replying!

By array I actually mean a UDT result set. By row/column I think I would be referring to dimensions within the array. I'm not sure what the correct terminology would be but for example;

Public Type RowArray
FieldA As String
FieldB As Integer
EndOfWeek As Integer
FieldC As Integer
FieldD As Integer
End Type
Dim RowArray() As Row

So the data starts in a sheet (actually multiple sheets). The data is loaded into various arrays (UDTs) which are then joined and compared to produce new calculated fields and a new result set that is written back to a different sheet. An ETL process written entirely in VBA - it's a headache!


To revise my pseudo code with more info about the array in my example I am trying to acheive something like this after loading the (UDT) result set;

For idxRow = 1 To UBound(RowArray)
VARIABLE = ((CCur(5 * _
RowArray(idxRow).FieldA + _
RowArray(idxRow).FieldB / _
RowArray(idxRow).FieldC )) 'result of this calculation

'divided by the result of this calc:
÷ count[all rows in the RowArray where EndOfWeeks equal to RowArray(idxRow).EndOfWeek])
Next row

Using SQL I would have no problem scripting this logic but as you can tell from my insane pseudo code- I really don't know VBA that well.

 

[JosieSlift]

What are the permissible values for endofweek ?

date... friday of each calendar week

 

[JosieSlift]

mintjulep:

By the way, you understand that your logic will result in the value of VARIABLE being based solely on whatever is in the LAST row of the array, right?

i left a lot of the code out but this bit of logic iside of another loop that walks through the rows appropriately

 

[SkipVought]

"I would assume ..."

"I am guessing ..."

"...and imagining that ..."

JosieSlift, help us, AND yourself, out.

Best we can do is hypothesize regarding what your pseudo code means.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JosieSlift]

Skip I am not skilled with VBA, this project was thrown at me and I'm having to work with what I do know. I'm trying to be as clear as I can and I apologize for any abiguity. Thanks for your patience.

 

[SkipVought]

Please answer the posted questions and you'll get lots of help.

We need to understand your intent. Please explain without resorting to code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

As electricpete and I both stated unambiguously:

You gotta' count 'em.

 

[JosieSlift]

Alright this will be a long post but I will try to re explain my situation in plain english AND address the questions posted by you guys in order....

The situation in plain english:

I have a dataset, an array loaded from a sheet in Excel, with a bunch of integer columns and a date column. The date column holds the date of the friday for each week in the dataset. What I need to do is, for each row, do some comparative math and derive a value using the integer columns. I then need to take this value, and again for each row, compare it against a value that needs to be derived from the array as a whole (not just one row).

to bring up psuedo code again....
(RowLevelField1 + RowLevelField2 + RowLevelField3) / ArrayLevelCalculatedVariableValue

I am thinking that maybe I need another array and/or some more looping. Working with datasets like this would be so much easier in a database

Now to answer all of your questions:


1. SkipVought

1a:
Are you referring to a RANGE on a SHEET, since you refer to ROWS and COLUMNS?

Answer:
By rows/columns I am referring to dimensions in an array

1b:
How are you defining ARRAY, which, BTW, is a VBA Reserve Word (Not an Excel Object)?

Answer:
by ARRAY I mean a specific array, I changed the name of the array to "ARRAY" without considering that it is a key word (sorry!)

1c:
How are you defining Columnn?

Answer:
column in the array

1d:How are you defining EndOfWeek?
date value corresponding the the friday of each week in the data



2. electricpete

2a:
I would assume you are working with an array, since ubound wouldn't work with a range.

Answer:
Correct, an array... not a range

2b:
I would also assume you have renamed the name of the array from some valid name to "array". Array is not a valid name for an array or any other variable... it is a reserved word as identified above.

Answer:
Again correct! I renamed the array without considering that it is a key word.

2c:
And by the way, where are the endofweek values stored? Another field of your database... for example MYARRAY(row).Column4 ... MYARRAY(row).endofweek ?

Answer:
Correct a third time... EndOfWeek is stored as a field in an array.

2d:
What are the permissible values for endofweek ?

Answer:
Dates... friday of each week

2e:
I am guessing you need another loop through your data before you get to your code above to count the number of rows containing each value of endofweek and store them in counter variables for each value of endofweek.

Answer:
I think you are correct! Can you elaborate?


2. mintjulep

3a:
Is EndOfWeek one of the columns in the array? You'd need to loop through and count them.

Answer:
Yes

3b:
I would also assume you have renamed the name of the array from some valid name to "array". Array is not a valid name for an array or any other variable... it is a reserved word as identified above.

Answer:
Again correct! I renamed the array without considering that it is a key word.

3c:
Maintaining your (interesting) syntax, and imagining that the endofweek is stored in column4:

For row = 1 To UBound(ARRAY)

If ARRAY(row).column4 = EndOfWeek then Count = Count + 1

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

ANSWER = VARIABLE / Count

Next row

Answer:
I don't think this is exactly what I am shooting for... For example ARRAY(row).column4 through ARRAY(row).column10 could all equal EndOfWeek.. kind of an example of what I would want to see as output for ARRAY(row).column4 would be:
EndOfWeek TotalRowsForThisWeek
1/1/1900 6

basically group on EndOfWeek and count the number of rows for that date.

3d:
By the way, you understand that your logic will result in the value of VARIABLE being based solely on whatever is in the LAST row of the array, right?

Answer:
This little bit of code is encapsulated in a couple levels of looping that I think accounts for the issue you are trying to alert me to (although I do not fully understand).

 

[mintjulep]

Does each row in the array contain data columns for one and only one week?

Does each row in the array contain one and only one date column?

 

[mintjulep]

To elaborate on 3d, consider the simple loop:

For count = 1 to 2
variable = f(count)
next count

which is what you originally posted, the value of variable is a function of count.

To further simplify, consider that F(count) simply is count. So:

For count = 1 to 2
variable = count
next count


First time through, variable = 1.

Second time through, variable = 2. The fact that variable was equal to 1 a moment ago has been forgotten.

 

[JosieSlift]

mintjulep:

Does each row in the array contain data columns for one and only one week?

answer: there can be more than one row for a given EndOfWeek date. For example if there are 3 campaigns all falling in week 1/1/1900, then there will be 3 rows with EndOfWeek = 1/1/1900. Actually to continue this example what I would be trying to do is:

For Row1; add two column values together, and then divide the result by three (since there are 3 other rows for this EndOfWeekdate).

Does each row in the array contain one and only one date column?

answer: there are other date columns but they are not relavent. One of the date columns represents the actual day a marketing campaign will start, the other column represents the friday for that week.

 

[JosieSlift]

In response to:
"To elaborate on 3d, consider the simple loop...."

Ok I follow you... luckily this is not a problem because the values are wrtten to another array before "count" is incremented... the value of the variable is stored in a row in another array before the next iteration of the loop

 

[SkipVought]

Is there a reason why you want to use an array, rather than the ranges in the sheet? There are conditional counting and summing that can be done much easier with ranges, for instance...

"to count the number of rows containing each value of endofweek"

Might look something like...

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 
dim r as range
VARIABLE=0
for each r in [TheEntireLeftColumnRange]
   VARIABLE = VARIABLE + (5 * _
      Cells(r.row, r.column+1) + _ 
      Cells(r.row, r.column+2) / _ 
      Cells(r.row, r.column+3)) / _
      Application.Countif( _
         Cells(r.row, r.column+4).entirecolumn, _
         Cells(r.row, r.column+4))
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

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

Yes, yes it is.

 

[JosieSlift]

Skip I think it would be too complicated to change at this point. There is a LOT of code, and like 3-4 different arrays that are all being bumped against one another and transformed into a final result set. I think changing the code too drastically would be a serious head ache.

I do plan to redesign this tool but it will be .NET and SQL based... For now I just need to get what they have working.

 

[JosieSlift]

Thanks again for your help and patience guys

I think the info you have given me will get me started down the right path