How to use VBA code - Find value from cell in different column and multiply by another cell
Category: [Excel] Demo Available
How to use VBA code - Find value from cell in different column and multiply by another cell
Requirement:
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.
Solution:
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.
Code:
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.