CODEWITHSUNDEEP
vbaexcelexcel-2010
this.guy
this.guy

Reputation: 91

Accumulate cell values in Excel using VBA

TL;DR So my only problem is that I need the cell values to accumulate, probably the whole explanation wan't necessary but you will get a better grasp of my situation if you read through it.

I've searched around and haven't found anything regarding this special scenario. I have this excel sheet https://i.sstatic.net/7cDDg.png with the first column/row indexes written out. (i removed unnecessary stuff to avoid confusion)

The code may be confusing but I will in writing explain how the code works as if i were to run it while you can check the code. The problem will be mentioned at the end.

So first the code:

   ws1.Activate
   Dim BLIrows As Integer, A As Range
   Dim newRow As Integer


   For BLIrows = 41 To 52
     For newRow = 14 To 30
       With ws1
         If .Cells(BLIrows, 1).Value > 1 Then                     'Make sure to only do this for non empty cells
          Dim x As Long                                                   '(they will be longer than 1 char)
          Dim y As String
          Dim element As Variant
          For Each element In .Cells(BLIrows, 1)
            y = .Cells(BLIrows, 1).Value                             'Save it's value for the search in the EFG14 list
            If y = .Cells(newRow, 5).Value Then                 'The search in the EFG14 list
              x = .Cells(newRow, 4).Value                          'Save the number when found an identical value 
                                                                                  '<-- MISSING CODE / PROBLEM
                 If y = .Cells(BLIrows, 1).Value Then             'Make sure to put the number in the correct row
                    .Cells(BLIrows, 5).Value = x                     'Putting the number
                 End If
            End If
          Next element
         End If
         If Not .Cells(BLIrows, 5).Value > 0 Then  'If anything (like Auto-pilot site) isn't in the EFG14 column
         .Cells(BLIrows, 5).Value = 0                   'then give it a 0
         End If
       End With
     Next
   Next

And here's how it's working written in text:

The code executes and it begins at Cell A41. It finds "P4250" first, as it's the first in this column, however I'm gonna focus on "P33-0236" since it appears only once in the EFG14 list (you will understand why later). So the code finds "P33-0236" at Cell A44. After it has that value, it will now search for it in the EFG14 column. As you suspect, it will find it's identical value in cell EFG21. The code then saves the number written in the same row but different column, in this case cell D21. With the number in D21 the code now searches the A40 column (where it began) for the value that retrieved the integer value (obviously "P33-0236") and finds it in A44, this because the number needs to be put in the same row but different column relative to A44 in this case. So it finds "P33-0236" in A44 and moves to a different column in the same row (E44). Here it will paste the integer value found in the D column.

Basically this explains a trial run of my vba code.

Now the PROBLEM

The problem is that when the integer values are copied to the Cost column (E40) it doesn't accumulate the values. For example, if the code executes with P4250 it will find "P4250" three times in the EFG14 column. The value then in it's equivalent Cost column should actually be, 28440 + 47400 + 47400 = 123240 however it's only 47400 (because it's last "P4250" found).

As the title suggest, I would like the values of the same "P" numbers to add when putting them in the cost column.

Don't focus on how the code is written - it works - unless it's something major. The only thing important is the accumulate code.

Upvotes: 0

Views: 1703

Answers (1)

leowyn
leowyn

Reputation: 128

If you want the value to be the sum of the current value plus the newly found value, your code will need to add them together before assigning the new value to the cell.

As it is now, it looks like you are just assigning the new value to the cell.

Perhaps something like this (assuming this is where you want the values to be additive):

.Cells(BLIrows, 5).Value = .Cells(BLIrows, 5).Value + x

Upvotes: 2

Related Questions