VBA, pulling values together to do a comparison

  • Hi,


    my current code does


    1) Opens my desired workbooks via a picker button on my master workbook (Just need to compare 2 at once)
    2) Delimits by pipe
    3) concatenates columns B and C (into col D)


    I am stuck here, my goal is to compare column A numbers from both opened workbooks on my master sheet.


    Should I vlookup my values from one workbook to another and bring in the numbers and subtract?


    How can I get my values in my master sheet and do a nice little subtraction? its tough, like I looked at some code to put my sheets into master but you have to state the master sheet and the current sheets to copy (sheet names will always be dynamic names so this kind is useless)


    Any help would be appreciated, Sorry i am a beginner.


    Current code:


  • Re: VBA, pulling values together to do a comparison


    When you say you want to compare the numbers in column A, what do you mean exactly?


    You might mean that you want to indicate elsewhere whether the same or different values appear on each row, or perhaps determine whether the number on one sheet appears anywhere in the other sheet(s)...


    Please clarify. The more detail the better. :)

  • Re: VBA, pulling values together to do a comparison


    Quote from Infomage;774260

    When you say you want to compare the numbers in column A, what do you mean exactly?


    You might mean that you want to indicate elsewhere whether the same or different values appear on each row, or perhaps determine whether the number on one sheet appears anywhere in the other sheet(s)...


    Please clarify. The more detail the better. :)



    Sorry.


    So both files are identical set, Column A has #'s. When I say i want to compare, I mean the difference between them.
    Now each number has a country and city assigned to it, B and C respectively. Also in both sheets.
    Now they are not in order, so new york usa may be in row 1 in sheet A, and row 30 in sheet B, So there must be some sort of lookup? Correct?


    The numbers are not identical on both workbooks, but the citys and countries are. if they arent the result should just be N/A or something.


    Is that more helpful?

  • Re: VBA, pulling values together to do a comparison


    Perhaps this sample shows more detail.


    Sheet 1 would be a workbook in reality, same with sheet 2.
    and desired result would be my master sheet with the button.


    Of course, my way may be abit longer, so if anyone has ideas on better compiling this, it would be appreciated. ie. the concat may not be necessary?

  • Re: VBA, pulling values together to do a comparison


    Quote from StephenR;774365

    So the two source tables are in different files and you want the results in a third file?


    Yes. Precisely. Unless you think of a better way of displaying?

  • Re: VBA, pulling values together to do a comparison


    Well you'll need to add workbook references, but this should work for your example file. If you sorted both tables before you wouldn't need the concatenate if they contain the same items

  • Re: VBA, pulling values together to do a comparison


    Quote from StephenR;774371

    I can't see where you define your file names? Do the two files have specific names or can they be anything?


    They will change slightly, so no they wont always be the same name,

  • Re: VBA, pulling values together to do a comparison


    Quote from StephenR;774447

    Can you answer my previous question please. I don't understand this line, it errors for me

    Code
    errCheck = UserInput.FileDialogDictionary(fileNames)



    Sorry Stephen , User Input is just a file picker functionI have in another module.
    I can post that too, Name the module 'UserInput' :


  • Re: VBA, pulling values together to do a comparison


    Try this, you will probably have to change some references. It has a slightly different approach to selecting the files - just select the two at the beginning.

  • Re: VBA, pulling values together to do a comparison


    Do I combine above code to my original?



    Or is this fine on its own? (keep in mind im opening .txt files that are piped, (not delimited))


    Running on its own I get error on :

    Code
    [COLOR=blue]Set[/COLOR][COLOR=#333333] rWB1 = wb1.Sheets(1).Range("A2").CurrentRegion [/COLOR][COLOR=#006400]' may have to change sheet[/COLOR]


    What is currentRegion?

  • Re: VBA, pulling values together to do a comparison


    OK, try this, replacing all your code

  • Re: VBA, pulling values together to do a comparison


    Wow thanks, seems to work, however I get an error on :


    Code
    n = Application.Match(r, rWB2.Columns(4), 0)

Participate now!

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