Help: I think i need to use an array...?

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
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.

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
Many Thanks,
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
so.... after some trawling... i think i need to load the 'contracts' values into an array, search it and then calculate the vales... sounds good but i still dont know how to do it yet.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,045
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub NRGZ()
   Dim Cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Contracts")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Dic.Item(Cl.Value) = Dic.Item(Cl.Value) + Cl.Offset(, 2).Value
      Next Cl
   End With
   With Sheets("Totals")
      For Each Cl In .Range("A3", .Range("A" & Rows.Count).End(xlUp))
         If Dic.Exists(Cl.Value) Then Cl.Offset(, 5).Value = Dic(Cl.Value)
      Next Cl
   End With
End Sub
 
Solution

NRGZ

New Member
Joined
Jul 23, 2021
Messages
47
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
hahahaha yer just as i was leaving yesterday that popped up in 'similar threads'!!! so yes indeed that is what i'm looking for!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,045
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,148,339
Messages
5,746,177
Members
423,998
Latest member
eakenila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top