Copy Match from 2 different Workbooks & Copy to worksheet in vba macro

  • Hi,


    I just need a little bit of help with this code.


    I need to match 2 different columns in 2 different workbooks. I need to match column b (sku) on workbook datafeed.xlsx to workbook result.xlsx on column b (sku), as shown on the code, but the difference is if column I (price) workbook datafeed.xlsx is the same as price on column J (seller price) workbook result.xlsx. Then copy results (only the same & not different) to new sheet named "same price", However, I need to have B column copied over as well as the same price. FYI the b column is sku and I need to know what price it is referring to based off the sku. So 2 things copied the sku and the same price onto a new worksheet called "same price" in result.xlsx.


    If you need examples of workbooks I can provide those?


    Code
    Dim w1 As Worksheet, w2 As Worksheet    Dim c As Range, FR As Long       Set w1 = Workbooks("datafeed.xlsm").Sheets("datafeed")     Set w2 = Workbooks("result.xlsx").Sheets("Sheet 1")          For Each c In w1.Range("b2", w1.Range("b" & Rows.Count).End(xlUp))         FR = 0         On Error Resume Next         FR = Application.Match(c, w2.Columns("b"), 0)         On Error GoTo 0         If FR <> 0 Then w2.Range("J" & FR).Value = c.Offset(, 7)   Next c
  • Based on what you've provided and with no sample data to work on, I've mocked up a macro (called FindMatchingPrice) in the workbook called Datafeed.xlsb
    I've also mocked up the workbook called Result.xlsx


    This will match the SKU and price on the 'datafeed' sheet in the Datafeed workbook with the first sheet in the Result workbook.
    It will then write matching SKU and price details to a sheet called 'same price' in the Result workbook.

  • Hi,


    Thanks for your help on this. I am sure your code works great but something strange is happening or i am doing something wrong. When i open Result.xlsx and open Datafeed.xlsb and run the code from Datafeed.xlsb it comes up with an error "Result workbook is not open". But i opened it and it says its not open? Its an exact match with your code and Result.xlsx.


    thanks

  • gijsmo,


    perfect that worked! Thank you! Also, I can tell that you are an organized and excellent coder. I have some coding I have been working on for a long time and I need some help. I know that I can't post it on this post because it's related but not directly related. I am willing to pay a little to have some separate coding done. Do I post it on the paid forum and then you reply or send you a forum message if you're interested of course?


    thanks,


    Again

  • Thanks, and yes, I've been coding for more decades than I care to remember and at least 10 years in VBA.


    I'm no moderator but my guess is you need to post your request for hired help on the https://www.ozgrid.com/forum/forum/hire-help forum.


    And, yes, whilst I'm probably qualified to code for hire, I don't believe I've got the number of posts required yet for the ozgrid hired help forum but I'm sure there are plenty of others who can help.

  • Hi gijsmo,


    I spoke too soon the code works great when there is something that matches, but if something doesn't match it says Run-time error 9 subscript out of range. I am assuming that it should say 0 matches instead of an error message? I tested it both ways and the matches work perfectly fine but no matches thus the error. I would have gotten back to you sooner but I always had matches and no matches.


    thanks

  • Hi,


    This works exactly how its supposed to. However, I am having an issue I hope anybody can help me with. Bottom line is I have a distributor that sends me my cost pricing and I then I mark it up and put it on Amazon. This macro is supposed to protect against any cost pricing to go up on Amazon if the markup is not applied. However, the pricing I need is not an exact match on price but up to $10.


    So let's say the cost price is $10 and with the markup doesn't work properly and marks that SKU up to $13. Then I would need that SKU pricing captured because it's within the $10 range. The code does an exact match and I was hoping instead of an exact match to change the code to be within $10?


    any help would be appreciated,


    thanks

  • For clarification, the examples in this thread use a Datafeed file and a Result file.


    If there is an SKU in the datafeed file and the price is say $120, then assuming there is a matching SKU in the Result file would a match be found if the seller price is $110 to $130 (ie, plus or minus $10) or $120 to $130 (plus $10 only) or some other variation ?

  • Thanks gijsmo for getting back to me and your quick response. What i am looking for from your example it would be $120 to $130. Anything from $120 to $130 would need the result, or $100 to 110, $80 to $90 etc.


    thanks

  • Ok, the revised sample files attached should sort that out.


    The new datafeed v4 file has a "Maximum variation" cell value which you can change (it's set to $10 at the moment). The $10 value is a named range so the macro is expecting to find this range.
    There's not much in the way of validation so user beware (eg, it's not checking this value is numeric).


    Also attached is a new sample Resultv3 file for testing.

  • Its really close to what i need but i need to keep the same value from j column in results worksheet and post the results to same price worksheet.


    Here is an example from your example workbooks.


    Datafeed
    B4356 - $150


    Result3
    B4356 - $158


    Its within the $10 range, but i need it to post $158 in the same price instead of $150. Better yet if it posted both prices from Datafeed and Result3 to same price then i can see where the discrepancy of the price is at. But either way is fine. I just really appreciate it.


    Sorry, for not explaining this correctly to you.


    Thanks Again

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!