# Find multiple values

• ### Question

• I have a first sheet

And a second sheet

I need to find client code in the sheet2 and add sum(Amount) in sheet 1

What I have try

nomClient = Range("B" & i).Value

Set cel = Cells.Find(What:=nomClient, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

My problen is Find can't do a sum , how to modify it ?

Many thanks in advance

Wednesday, November 16, 2016 9:09 AM

### All replies

• You can use Excel Worksheetfunctions in VBA, like SUMIF.  I'm not sure if this is the logic you want, or the direction you want (from Sheet1 or from sheet2) since your numbers don't add up in either direction, and it is unclear what you want to do with the sum after you find it, but this is how you use the function:

Sub TestMacro()
Dim dSum As Double
Dim i As Integer

i = 2

dSum = Application.WorksheetFunction.SumIf(Worksheets("Sheet1").Range("A:A"), _
Worksheets("Sheet2").Range("A" & i).Value, Worksheets("Sheet1").Range("B:B"))

MsgBox "The sum for """ & Worksheets("Sheet2").Range("A" & i).Value & """ is " & Format(dSum, "0.00")
End Sub

Wednesday, November 16, 2016 3:30 PM
• Hi,

Your question is ambiguous.

My Question:

1. In Sheet1 heading "Client" is in Column A but I can see in your code you are searching values from Column B.

2. Can you simply take a valid example in Sheet 1 and Sheet 2 and highlighted result you expect. A simple screenshot will help.

Vish Mishra

Wednesday, November 16, 2016 3:35 PM
• Hi

Did Bernie Deitrick, Excel MVP 2000-2010 response helped you? If yes please marked it as answered in order to make this forum neat.

Regards,

Vishwa

Vish Mishra

Friday, November 18, 2016 11:12 AM