Find All instances and sum them up VBA

mysticmario

Board Regular
Joined
Nov 10, 2021
Messages
135
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

JamesCanale

Well-known Member
Joined
Jan 13, 2021
Messages
691
Office Version
  1. 365
Platform
  1. Windows
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.
 

mysticmario

Board Regular
Joined
Nov 10, 2021
Messages
135
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,176,312
Messages
5,902,427
Members
434,975
Latest member
snoski

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