Excel/macro that will match two different column from two different sheets
I badly need help, I am working on a file that has two sheets.
I need a macro that will match EMPID and PROCODE in Sheet 1(April (2)) with Sheet 2 (Compre List), if found the entire row in Sheet 2 should be deleted then the EMPID[C] and PROCODE[D] should be posted in the next blank row of Sheet 1 ([B] and [D] of Sheet 1).
Few things to note.
1. Above square brackets represent column letters.
2. I need the macro to look for the first blank row between row 13 to row 25 only of sheet 1, not from the bottom of the sheet.
3. The macro will be run from different sheet (April (1), April (2), April (3), etc.)
Please see the link of the workbook for your reference.
This macro will do the job
Dim cursheet As Worksheet, checksheet As Worksheet, looper As Long, checker As Long
Dim blankcell As Long
Set cursheet = ActiveSheet
Set checksheet = Worksheets("Compre List")
blankcell = WorksheetFunction.CountA(cursheet.Range("A13:A25")) + 13
checker = checksheet.Cells.SpecialCells(xlCellTypeLastCell).Row
For looper = 2 To checker
checksheet.Cells(looper, 10).FormulaR1C1 = "=SUMPRODUCT(--('April (2)'!R13C2:R25C2='Compre List'!RC[-7]),--('April (2)'!R13C4:R25C4=RC[-6]))"
For looper = checker To 2 Step -1
If checksheet.Cells(looper, 10).Value > 0 Then
'have found a cell with data
cursheet.Cells(blankcell, 2).Value = checksheet.Cells(looper, 3).Value
cursheet.Cells(blankcell, 4).Value = checksheet.Cells(looper, 4).Value
blankcell = blankcell + 1
I have hard coded the reference to the compre list sheet, but the rest of the macro will run on whatever sheet is active. Sorry for the delay in replying, was having problems getting the original file downloaded and had to try from an alternative location.