vba range find to get result of all occurrence

[playtime]

Here is the range find vba code i have which is working fine but, working for only the first occurrence.
I am not vba expert hence looking for help to include a loop to get sum of all occurrence as result

With WsOi

    For Each Cel In WsOi.Range("A2:A" & Oi_LR)
        On Error Resume Next
        
        V = "not found"
        V = WsOpnIntNse.Range("B1:B" & nseoi_LR).Find(Cel, lookAt:=xlWhole, SearchDirection:=xlNext).Offset(, 11)
        Cel(, .Columns.Count).End(xlToLeft).Offset(, 1) = V
        
        On Error GoTo 0
        Next Cel

 

[EXCEL MAX]

Hello Playtime,
try to use worksheet "SumIf" function in VBA code

Option Explicit

Sub UseSumIfFunction()
    
    Dim varWsOi  As Worksheet
    Dim varOi_LR As Long
    Dim varSearchParametar As String
    Dim varResult
    
    Set varWsOi = Sheets("YoursheetName")
    varSearchParametar = "YourParametar"
    varOi_LR = varWsOi.Cells(Rows.Count, 1).End(xlUp).Row
    varResult = WorksheetFunction.SumIf(Range("B1:B" & varOi_LR), varSearchParametar, Range("A1:A" & varOi_LR))
    MsgBox (varResult)
    
End Sub

 

[playtime]

Hello Playtime,
try to use worksheet "SumIf" function in VBA code

Option Explicit

Sub UseSumIfFunction()
   
    Dim varWsOi  As Worksheet
    Dim varOi_LR As Long
    Dim varSearchParametar As String
    Dim varResult
   
    Set varWsOi = Sheets("YoursheetName")
    varSearchParametar = "YourParametar"
    varOi_LR = varWsOi.Cells(Rows.Count, 1).End(xlUp).Row
    varResult = WorksheetFunction.SumIf(Range("B1:B" & varOi_LR), varSearchParametar, Range("A1:A" & varOi_LR))
    MsgBox (varResult)
   
End Sub

I am sorry but, I dont think sumif works for me as your codes give single result whereas my codes which i have mentioned is a range finder and find all the values and fetch them one by one
what i need is to include a loop.
for a better understand i will update you with screenshot shortly
Thanks

 

[playtime]

Hi There,
I have uploaded the sample sheets,
Till last weeks there were only single names in column A and the my codes fetch the amount from sheet"Data" and put it in sheet"Result" in last empty available column one by one
But now the names are appearing 3 times so I need to sum or club the respective amount as a result.
All I need is to use the loop with sum in my above written codes.


Thanks

 

[EXCEL MAX]

Is this a little bit closer?

Sub UniteValues()

    Dim WsOi  As Worksheet, WsOpnIntNse As Worksheet
    Dim cel As Range
    Dim Oi_LR As Long, nseoi_LR As Long
    Dim v
    Dim varLastCell As Long
   
    Set WsOi = Sheets("Data")
    Set WsOpnIntNse = Sheets("Result")
    Oi_LR = WsOi.Cells(Rows.Count, 1).End(xlUp).Row
    nseoi_LR = WsOpnIntNse.Cells(2, Columns.Count).End(xlToLeft).Column
    
    For Each cel In WsOi.Range("A2:A" & Oi_LR)
        Set v = WsOpnIntNse.Range("A1:A" & Oi_LR).Find _
            (cel, lookAt:=xlWhole, SearchDirection:=xlNext)
        If Not v Is Nothing Then
            WsOpnIntNse.Cells(v.Row, nseoi_LR + 1) = _
                WsOpnIntNse.Cells(v.Row, nseoi_LR + 1).Value + cel.Offset(0, 1).Value
        Else
            varLastCell = WsOpnIntNse.Cells(Rows.Count, 1).End(xlUp).Row
            WsOpnIntNse.Cells(varLastCell + 1, 1) = cel.Value
            WsOpnIntNse.Cells(varLastCell + 1, nseoi_LR + 1) = cel.Offset(0, 1).Value
        End If
    Next cel
    
End Sub

 

[playtime]

Hi,
Yes its pretty close to......
I tried and found that while searching and fetching the results from sheet"Data"
if the Name is not there it is creating the Name in Sheet"Result" in last row and putting the respective value
Whereas it has to be written "not found" in sheet "Result" if Name is not found

I tried 2 hours but, no luck

 

[playtime]

I think I can easily describe visually

 

[EXCEL MAX]

Here it is Playtime.
Sorry you waiting to long. I have made new concept.
Also I have rename your variables, it was easier way to understand the code.
Your image was very helpful.
The code works fine and I think that is thing you are looking for.

Sub SumSameValues()

    Dim varWorksheet1 As Worksheet, varWorksheet2 As Worksheet
    Dim varCell1 As Range, varCell2 As Range
    Dim varWS1Rows As Long, varWS2Columns As Long, varResizeRow As Long
    Dim varWord
    Dim varWS2Rows As Long
   
    Set varWorksheet1 = Sheets("Data")
    Set varWorksheet2 = Sheets("Result")
    varWS1Rows = varWorksheet1.Cells(Rows.Count, 1).End(xlUp).Row
    varWS2Columns = varWorksheet2.Cells(2, Columns.Count).End(xlToLeft).Column
    varWS2Rows = varWorksheet2.Cells(Rows.Count, 1).End(xlUp).Row
    varResizeRow = 2
    For Each varCell1 In varWorksheet2.Range("A2:A" & varWS2Rows)
        For Each varCell2 In varWorksheet1.Range("A2:A" & varWS1Rows)
            Set varWord = varWorksheet1.Range("A" & varResizeRow & ":A" & varWS1Rows).Find _
                (varCell1.Value, lookAt:=xlWhole, SearchDirection:=xlNext)
            If Not varWord Is Nothing Then
                If varWord.Row = varResizeRow Then GoTo NEWCEL
                varResizeRow = varWord.Row
                varWorksheet2.Cells(varCell1.Row, varCell1.Column + varWS2Columns) = _
                varWorksheet2.Cells(varCell1.Row, varCell1.Column + varWS2Columns) + _
                varWorksheet1.Cells(varResizeRow, 2).Value
            Else:
                varWorksheet2.Cells(varCell1.Row, varCell1.Column + varWS2Columns) = _
                    "Not Found"
                GoTo NEWCEL
            End If
        Next varCell2
NEWCEL:
        varResizeRow = 2
    Next varCell1

End Sub

 

[playtime]

EXACTLY AS I WANTED?
THANKS*1000k

 

[EXCEL MAX]

Ok, you can do one more thing to prevent error if in the sheet "Result" there is nothing in the column "A".
Between two "Foreach" loops add this line...

For Each varCell1 In varWorksheet2.Range("A2:A" & varWS2Rows)
    If varCell1.Value = "" Then Exit Sub
    For Each varCell2 In varWorksheet1.Range("A2:A" & varWS1Rows)