# Find a String and add up corresponding numeric valuse accross different worksheets.

#### Nizamuddin

##### New Member
Hi,

I have a workbook with hundreds of worksheets, with data in the first 5 coloumns the first 3 coloumns have string with ID, Name, and Description and the rest 2 coloumns have integer numbers.
I would like to have a summary sheet with the list of few Names for which I need to find the total sum of corresponding values in every sheet present in the workbook.
I want to pick up the first name in the summary sheet find it in Col 2 and when found add up the total of col 4 next to the name in the summary sheet. and similarly the sum of col 5 in the next cell of the summary sheet and loop thru all the worksheets present, similarly continue doing for the next name until a blank cell is reached.
The number of sheets in the workbook may vary and so can the items to be searched for... hence all of this needs to be dynamic.

Any help with a code that can do this task would be greatly appreciated !

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Anthony47

##### Well-known Member
Not so bad for your first topic...
You may try this macro:
Code:
``````Sub nizam()
SumSh = "Summary" '<<<Name of the summary sheet
Sheets(SumSh).Activate
If ActiveSheet.Index > 1 Then
MsgBox ("Please move Summary sheet in position 1, then retry")
Exit Sub
End If
For N = 1 To 1000   '<<< Max 1000 names to search
Sheets(SumSh).Activate
LName = Range("A1").Offset(N, 0).Value
If LName = "" Then
MsgBox ("Completed, maybe"): Exit Sub
End If
RwName = Range("A1").Offset(N, 0).Row
For Sh = 2 To ThisWorkbook.Worksheets.Count
Sheets(Sh).Activate
For Rw = 0 To Cells(Rows.Count, 2).End(xlUp).Row 'Test col 2 to count how many lines in sheet
If Range("B1").Offset(Rw, 0).Value = LName Then
Sheets(SumSh).Cells(RwName, 2).Value = Sheets(SumSh).Cells(RwName, 2).Value + Range("B1").Offset(I, 2).Value
Sheets(SumSh).Cells(RwName, 3).Value = Sheets(SumSh).Cells(RwName, 3).Value + Range("B1").Offset(I, 3).Value
End If
Next Rw
Next Sh
Next N
End Sub``````

From excel, open the vba editor via Alt-F11, the Menu /Insert /Module; copy the macro and paste it into the white frame of vba editor.
Review the lines marked "<<<" to cope with your environment.
Put a Summary sheet as sheet number 1 in your workbook, with the list of names from A2 down (use line 1 for the headers); the first blank cell means "end of list".
The macro should read the name on Summary, then scan all the rows of the sheets, and if the "name" corresponds to the value in Col. B then it will get Col. D and E and sum these values to Summary's col. B and C.

My testing was fair, but my testing bed is limited... so before any test create 2 backup copies of your data!

Let us know.

#### Nizamuddin

##### New Member
Thank you for your help Anthony...
But I got a compile Error unfortunately...

It read : Compile Error : End If with out Block If....

#### Anthony47

##### Well-known Member
The top of the macro should have been:
Sub nizam()
SumSh = "Summary" '<<<Name of the summary sheet
Sheets(SumSh).Activate
If ActiveSheet.Index > 1 Then
MsgBox ("Please move Summary sheet in position 1, then retry")
Exit Sub

I don't know what happened before.

Try again, bye.

#### Nizamuddin

##### New Member
I got rid of that compile erro and the macro seems to be working fine as coded...
but dint get the desired results due to my bad...

what changes will I have to do if the Name coloumn is C instead of B as mentioned before...
So its.... : Date, ID, NAME, Description, Num1, Num2

Thanks for your help....

#### Anthony47

##### Well-known Member
Modify this: If Range("B1").Offset(Rw, 0).Value = LName Then
to this:
Code:
``If Range("C1").Offset(Rw, 0).Value = LName Then``

bye.

#### Nizamuddin

##### New Member
Thanks...

Here's the final code that i'm using..
Sub nizam()
SumSh = "Summary" '<< Sheets(SumSh).Activate
If ActiveSheet.Index > 1 Then
MsgBox ("Please move Summary sheet in position 1, then retry")
Exit Sub
End If
For N = 1 To 1000 '<<< Max 1000 names to search
Sheets(SumSh).Activate
LName = Range("A1").Offset(N, 0).Value
If LName = "" Then
MsgBox ("Completed, maybe"): Exit Sub
End If
RwName = Range("A1").Offset(N, 0).Row
For Sh = 2 To ThisWorkbook.Worksheets.Count
Sheets(Sh).Activate
For Rw = 0 To Cells(Rows.Count, 2).End(xlUp).Row 'Test col 2 to count how many lines in sheet
If Range("C1").Offset(Rw, 0).Value = LName Then
Sheets(SumSh).Cells(RwName, 2).Value = Sheets(SumSh).Cells(RwName, 2).Value + Range("B1").Offset(I, 2).Value
Sheets(SumSh).Cells(RwName, 3).Value = Sheets(SumSh).Cells(RwName, 3).Value + Range("B1").Offset(I, 3).Value
End If
Next Rw
Next Sh
Next N
End Sub

My data looks something like this

Date ID Name Desc Count1 Count2
06/10 001 LDP xxxx 5 10
06/10 002 RDE xxxx 2 8

When I execute I get the Results as

Name
RDE Count1Count1Count1 Count2Count2Count2

( considering RDE was fount 3 times its displaying the coloumn heading 3 times in the summary sheet next to the name that was entered to find )
Also, when I execute the macro again it amends the result set instead of clearing what was in there already... so it displays Count1 6 times and Count2 6 times....

Thanks...

#### Anthony47

##### Well-known Member
This means that Count1 and Count2 are currently formatted as text.
Try modifying this
Sheets(Sh).Activate

to this:
Code:
``````  Sheets(Sh).Activate
Columns("D:E").Select
Selection.NumberFormat = "0.00"``````

Bye.

#### Nizamuddin

##### New Member
I tried this code ( Actullay "E:F" in my case ) and also manually changed the format of the coloumns to number...
Sitll getting the same out put with the colounm headings instead of the total sum...

Thanks,
Nizam.

#### Anthony47

##### Well-known Member
Oh my God!
I misinterpreted your point, and now realize what you mean, that require a correction to the macro as follows.
Wrong:
Sheets(SumSh).Cells(RwName, 2).Value = Sheets(SumSh).Cells(RwName, 2).Value + Range("B1").Offset(I, 2).Value
Sheets(SumSh).Cells(RwName, 3).Value = Sheets(SumSh).Cells(RwName, 3).Value + Range("B1").Offset(I, 3).Value
Right:
Code:
``````Sheets(SumSh).Cells(RwName, 2).Value = Sheets(SumSh).Cells(RwName, 2).Value + Range("B1").Offset(Rw, 2).Value
Sheets(SumSh).Cells(RwName, 3).Value = Sheets(SumSh).Cells(RwName, 3).Value + Range("B1").Offset(Rw, 3).Value``````

Hope now it's ok...
Bye.

Replies
5
Views
987
Replies
0
Views
503
Replies
1
Views
411
Replies
2
Views
736
Replies
4
Views
632

1,191,123
Messages
5,984,775
Members
439,910
Latest member
Flyingjoblo

### 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?

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