Macro to Find a match and update corresponding value

[laxminarayana]

Hello All,

I have something similar situation on my workbook. Sheet1 has large amount of data with Product name (column A) and current value (column D). There are some blank records in between too. Sheet2 has Product name (column A) and sold amount (column B). There are no blank records in between for this sheet. I'm trying to search column A of Sheet2 in column A of Sheet1. If match is found in Sheet1, then amount in Column D has to be deducted by the amount of sheet2 (column B). In short deducting the sold amount in sheet2 from the total amount in sheet1. I have attached a simplified version of what my worksheets look like. Can you please help with some code.

Thank you!

 

[BSALV]

Sub Deduct()
     Set c1 = Sheets("sheet1").UsedRange.Resize(, 4)            'has empty row in between
     Set c2 = Sheets("sheet2").Range("A1").CurrentRegion.Resize(, 5)     '1 contigious block
     c1.Columns(1).Name = "Prod_Store"
     a = [transpose(prod_store&"|"&offset(prod_store,,2))]      'c1, Columns 1&3 together in an array in memory

     For i = 2 To c2.Rows.Count                                 'loop through all data in sheet2
          r = Application.Match(c2(i, 1).Value & "|" & c2(i, 4), a, 0)     'find match product and store in A-column sheet1
          If IsNumeric(r) Then                                  'match found
               c1(r, 4) = c1(r, 4) - c2(i, 2)                   'decrease value for product&store in sheet1 with the value found in sheet2
          End If
     Next
End Sub

 

[laxminarayana]

Sub Deduct()
     Set c1 = Sheets("sheet1").UsedRange.Resize(, 4)            'has empty row in between
     Set c2 = Sheets("sheet2").Range("A1").CurrentRegion.Resize(, 5)     '1 contigious block
     c1.Columns(1).Name = "Prod_Store"
     a = [transpose(prod_store&"|"&offset(prod_store,,2))]      'c1, Columns 1&3 together in an array in memory

     For i = 2 To c2.Rows.Count                                 'loop through all data in sheet2
          r = Application.Match(c2(i, 1).Value & "|" & c2(i, 4), a, 0)     'find match product and store in A-column sheet1
          If IsNumeric(r) Then                                  'match found
               c1(r, 4) = c1(r, 4) - c2(i, 2)                   'decrease value for product&store in sheet1 with the value found in sheet2
          End If
     Next
End Sub

I have pasted the exact code in module and run the code. It did nothing. I'm I missing something.

 

[BSALV]

laxminarayana.xlsm
columns 1&2 instead of 1&3

Sub Deduct()
     Set c1 = Sheets("sheet1").UsedRange.Resize(, 4)            'has empty row in between
     Set c2 = Sheets("sheet2").Range("A1").CurrentRegion.Resize(, 5)     '1 contigious block
     c1.Columns(1).Name = "Prod_Store"
     a = [transpose(prod_store&"|"&offset(prod_store,,1))]      'c1, Columns 1&2 together in an array in memory

     For i = 2 To c2.Rows.Count                                 'loop through all data in sheet2
          r = Application.Match(c2(i, 1).Value & "|" & c2(i, 4), a, 0)     'find match product and store in A-column sheet1
          If IsNumeric(r) Then                                  'match found
               c1(r, 4) = c1(r, 4) - c2(i, 2)                   'decrease value for product&store in sheet1 with the value found in sheet2
          End If
     Next
End Sub