The code i have identifies the entry from TOTALS matches the entry in CONTACTS and then returns the value, that woks fine but if there are multiple entries of the same product code i need to get each value and add them together to give a grand total. I think i'm right in thinking that i would store the values in an array but i'm not sure how to code it.
(so easy to get out of your depth when your so early into learning VB....)
Thanks in advance.
o/
VBA Code:
Private Sub cntrct1()
' what product contract totals are 'contract commitments'
Sheets("totals").Activate
Dim y As Integer
Dim z As Integer
Dim prod As Integer
Dim prod0 As String
Dim matchPos As Integer
y = WorksheetFunction.CountA(Worksheets("contracts").Range(Worksheets("contracts").Cells(2, 1), Worksheets("contracts").Cells(999, 1)))
With Worksheets("contracts")
For z = 2 To y + 1
prod = Sheets("Totals").Cells(z, 1)
matchPos = Application.Match(prod, .Range(Worksheets("contracts").Cells(2, 1), Worksheets("contracts").Cells(999, 1)), 0)
If Not IsError(matchPos) Then
Sheets("Totals").Cells(z, 6).Value = .Cells(matchPos + 1, "C").Value
End If
Next z
End With
' check status only for testing
Cells(4, 10) = prod
Cells(4, 11) = y
Thanks in advance.
o/