# Dynamic Changing Data, Based on Criteria Store in Array, Sum array and store in Cell

1. ## Dynamic Changing Data, Based on Criteria Store in Array, Sum array and store in Cell

I have a vba macro which searches through a given data database. Each time the file is uploaded, the data is subject to change in size. I am trying to create an operation that will total data values based on know criteria in other columns. For example Column 2 has company identifying numbers, Column 4 has the data labels by company. Given this information I seek to identify the rows of data that belong to the same company and are labeled beginning with "TOTAL", suming its values. By doing so I am looking to store the calculated value in a row labeled "TRNS:"

My issue is that I cannot figure out how to create a vector/array ,when I do not know the total number of data points needed to be added together.

The code I have is provided, This might not be the best approach but it seemed to work until I was presented with the array problem.

Sub StoreArray()
Dim TValue As Variant 'Dimensions Array
Dim Cell As Range
Dim i As Integer
For i = 2 To 2000
If Cells(i, 2) = Cells(i - 1, 2) Then 'Limits Data TO ID Match
If Left(Cells(i, 4), 5) = "TOTAL" Then 'Limits Data To Total Match
'''''''''''''''''''''''''''''''''''''''''''''
'Start Storying Array Values
For Each Cell In Range("K1:K2000") 'Lookup Range
TValue = Cell.Value 'Store values in Array
Next Cell
'End Storing Array Values
'''''''''''''''''''''''''''''''''''''''''''''
End If
End If
Next i

'Store Array Values in CEll 'FInd last Company Totaling Row
If Left(Cells(i, 4), 5) = "TRNS:" Then 'Identifies Transfer Total
If Cells(i, 2) = Cells(i - 1, 2) Then 'Confirms Data ID match
' Cells(i,7) = 'Stores array value sum in given cell
End If
End If
'Process complete
MsgBox "done"

End Sub

I hope someone can help me out with this, Thank you in Advanced!!

2. ## Re: Dynamic Changing Data, Based on Criteria Store in Array, Sum array and store in Cell

Submit your workbook or an example workbook with data that represents your situation.

3. ## Re: Dynamic Changing Data, Based on Criteria Store in Array, Sum array and store in Cell

Sorry, I have attached a example file.

4. ## Re: Dynamic Changing Data, Based on Criteria Store in Array, Sum array and store in Cell

Is there going to be more than 1 company on the sheet or will it be all the same number "New COMP NO"?

Edit: I will go off the exact layout of your supplied sheet.

``Please Login or Register  to view this content.``
Note: There is probably some trick native formula which could do this but I am not that good at native formulas

5. ## Re: Dynamic Changing Data, Based on Criteria Store in Array, Sum array and store in Cell

Sorry about the delayed response, there will be more companies, the id match in the beginning is intended to filter each company one by one. The reason there would be 2 company ID is due to a change in client status. The new ID replaces the old one and groups all like company data together.

6. ## Re: Dynamic Changing Data, Based on Criteria Store in Array, Sum array and store in Cell

The provided code works great for just a one company instance, how can I get this to run for multiple companies. The attached workbook contains a multiple company example, that is more realistic to what Im trying to achieve. Attached to it are the 2 codes ive tried to get to work and have not been successful using array as well as the provided forum code. Kinda confused on the array approach and why it wont work, but any method to get this working will be a stress relief.

7. ## Re: Dynamic Changing Data, Based on Criteria Store in Array, Sum array and store in Cell

This is a brute force solution but I didn't have time to do anything too special and it should work privided the format stays the same:

``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)