This code works fine but if it finds two identical 'prod' values (which are product codes) then i would like it to store the value from each, add them together and then output to the cell already ready for the value.
I think i need to use an array? but i'm not how i would code it.
Many Thanks,
I think i need to use an array? but i'm not how i would code it.
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 = 3 To y + 2
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
End Sub