Find All instances and sum them up VBA

mysticmario

Active Member
Joined
Nov 10, 2021
Messages
323
Office Version
  1. 365
Platform
  1. Windows
Hi, I am using a code to find a cell value and then take the adjacent cell value to my report document: this is the code:
VBA Code:
'2.ORGANZIACJA'
ActiveSheet.Columns("D:D").Select
Set Cell = Selection.Find(what:="ORGANIZACJA", after:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If Cell Is Nothing Then
    Sheets(LastSheet).Range("S12").Value = 0
    Sheets(LastSheet).Range("T12").Value = 0
Else
    Sheets(LastSheet).Range("S12").Value = Cell.Offset(0, 1).Value
    Sheets(LastSheet).Range("T12").Value = Cell.Offset(0, 3).Value
End If
'------'

It works great but now I encountered a problem where i have multiple instances of this value in column. let's say the value is called "painting the element", I want to find all instances of this value in the column D, sum it up and "ship" it to Sheets(LastSheet).Range("R25")
Can't wrap my head around how to do it.
anyone can help?
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
VBA seems overkill for this.
MrExcelPlayground5.xlsx
DEFGHIJKLMN
1ThingValue1Stuff?Value3Other Sheet somewhere
2A15100Looking forValue 1Value 3
3B20103D105245
4C25106
5D30109
6E35112
7F40115
8G45118
9H50121
10I55124
11J60127
12K65130
13C70133
14D75136
15E80139
16F85142
17G90145
Sheet11
Cell Formulas
RangeFormula
M3M3=SUMPRODUCT(E2:E17,--(D2:D17=L3))
N3N3=SUMPRODUCT(G2:G17,--(D2:D17=L3))

This works for cell values being equal. If you want to find something in the column D, but not necessarily the whole thing, that can be done too.
 
Upvote 0
Even before your reply I thought of an idea where I force a =SUMIFS formula into one of the active sheet cells via vba and then import the result into another workbook as value.
Something like this
ActiveSheet.Range("X2").Formula= "=SUMIF(D:D;"Val";H:H)"
Sheets(LastSheet).Range("R25").Value =Range("X2").Value


Or sth similar
 
Upvote 0

Forum statistics

Threads
1,218,909
Messages
6,145,164
Members
450,591
Latest member
mba3170

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
Back
Top