How to use VBA code - Find value from cell in different column and multiply by another cell

< Back to Search results

 Category: [Excel]  Demo Available 

How to use VBA code - Find value from cell in different column and multiply by another cell




The user has a workbook with 2 sheets - one with database and one with exported data that macro takes from another files.


The user would need a code that would check each cell from column B in sheet with exported data and compare it with each cell from column A in the database sheet.


If it would find a match then the user would want the code to multiply cell next to the searched cell (column C) in the sheet with exported data by cell next to the found result (column B) in database sheet.


Then it should create a new column D in the sheet with exported data for the results.


If it wouldnt find a match then no action.






This macro assumes that you have headers in row 1 of both sheets and your data starts in row 2. Also, change the sheet names in the code to match your actual sheet names.

Sub CompareCols()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Sheets("Exported Data")
    Set ws2 = Sheets("Database")
    Dim v1, rng As Range, fn
    v1 = ws2.Range("A2", ws2.Range("A" & Rows.Count).End(xlUp)).Value
    For Each rng In ws1.Range("B2", ws1.Range("B" & ws1.Rows.Count).End(xlUp))
        fn = Application.Match(rng, v1, 0)
        If Not IsError(fn) Then
            rng.Offset(0, 2) = rng.Offset(0, 1) * ws2.Cells(fn + 1, 2)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub


Obtained from the OzGrid Help Forum.

Solution provided by Mumps.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to use VBA code to ccolour tabs based on tab/text number
How to use VBA code to insert a row at the top of a table
How to VBA code to count duplicates FAST
How to create VBA code to find next empty column and next empty row
How to use VBA to read data from one worksheet and copy to another formatted one
How to automate charts with 1 x - axis and multiple y - axis in VBA


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)