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
'Add Array Values Together
'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!!
Bookmarks