I'm looking for a vba solution for this search and add a formula task

[zigen9]

Hello all,

I have been always thinking for a solution for this task, I have spreadsheet A with a "column A" with a bunch model names(model name repeats in different rows) and "column B" with their prices.
I have another spreadsheet with a summary of these model names on "Column C" and addition to price on "Column D"

I would like to search C1 cell to "column A" and add D2 cell to "column B", problem is C1 repeats and do the same process until no more duplicates and search C2.
Really trying my best to explain this. Please shed me some light about this solution. Thank you.

 

[Joe4]

Welcome to the Board!

I think the best thing to do would be to show us a small sample of your data, and then show us what your expected results of that example should looke like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[zigen9]

Thank you very much. I'm sorry i'm new to this forum and would like to learn more from experts like you. I will prepare my data and post again later. Thanks again.

 

[Joe4]

Thank you very much. I'm sorry i'm new to this forum and would like to learn more from experts like you. I will prepare my data and post again later. Thanks again.

You are welcome.
Just be sure to continue posting in this thread (don't start a new one for the same question).

 

[zigen9]

Hello Joe4,

Please see attached the example for the task I am facing.

Step1 is to search E2 model name to column A and add F2 to column B cells, there will be multiple E2 names in column A that needs to add F2 number to column B.

Step2 is to search E3 model name to column A and add F3 to colmn B cells...

Just need to keep going until the end of the list on column E.

Is there a vba example that I can follow? Please let me know. Any help would be great. Thank you so much.

 

[Joe4]

If your values in column B really are formulas like that, adding up multiple values, then this VBA code should do what you want:

Sub AddValues()

    Dim lrA As Long, lrE As Long
    Dim rA As Long, rE As Long
    Dim m As String
    Dim v As Double
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lrA = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find last row in column E with data
    lrE = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Loop through all values in column E
    For rE = 2 To lrE
'       Get value to look up and its corresponding value
        m = Cells(rE, "E")
        v = Cells(rE, "F").Value
'       Loop through values in column A
        For rA = 2 To lrA
'           Check to see if values match
            If Cells(rA, "A") = m Then
                Cells(rA, "B").Formula = Cells(rA, "B").Formula & "+" & v
            End If
        Next rA
    Next rE
    
    Application.ScreenUpdating = True
    
End Sub

 

[zigen9]

Hello Joe4,

This is absolutely incredible!!! Thank you for this vba code and as well as the lesson in this code. Can I make a donation to you for a beer at least? I really do appreciate you for this.

Thank you again Joe4.

 

[Joe4]

You are welcome.
Glad I was able to help!

We don't accept donations, but feel free to make a donation to your favorite charity on my behalf!

 

[zigen9]

Thank you again Joe4. I will make this donation on your behalf.