Find multiple values RRS feed

  • 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.



    Vish Mishra

    Friday, November 18, 2016 11:12 AM