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