Macro To Import Data & Compare

  • Hi Guys,



    Brief description of what i want my macros to achieve: I've trying to import data from two Excel spreadsheets, and then compare the data on each list to find the missing elements in each list and then highlight the data row if it is missing. Still rather new in excel-VBA please go easy on me.. :)



    Kinda puzzled with the error i have here for my excel-vba codes while running the code below. Below i have attached a copy of my Excel file for you reference. Thanks for your help and time in advance.

  • Re: Macro To Import Data & Compare


    What is your error? On the sheet1 coding space, it will fail because "stringOfSheet2" is not defined there. Also, dynamicSheet1 and 2 are, as far as I can tell, useless. Also not sure where your going with the labels in sheet1, unless you just haven't used them yet.

  • Re: Macro To Import Data & Compare


    Oh yes, i'm still building on the codes so i have not fully implement everything yet. The problem i faced was to get the program to run the comparison after importing, but it always run into some error when i try to run it..

  • Re: Macro To Import Data & Compare


    I've tried moving stringOfSheet1 and stringOfSheet1up the line and the error gone but i'm still unable to run the Difference sub module with error subscript out of range? Any ideas, sorry rather new in programming

    Code
    1. Public CheckBoolean1 As BooleanPublic CheckBoolean2 As BooleanPublic stringOfSheet1 As StringPublic stringOfSheet2 As StringSub SetDefaults() CheckBoolean1 = False CheckBoolean2 = FalseEnd Sub

  • Re: Macro To Import Data & Compare


    Well from what I can see, your assigning values to stringOfSheet in one module and trying to access them in another which isn't going to work. If you add a watch to stringOfSheet1 in the module you'll see it is still initialized as a null string.

  • Re: Macro To Import Data & Compare


    You mean i just replace it with this and it should work? But why i'm still having the same error despite the changes, have i missed out something?


    Code
    1. Set ws2 = ThisWorkbook.Sheets(Sheets(2)) Set ws3 = ThisWorkbook.Sheets(Sheets(3))

  • Re: Macro To Import Data & Compare


    Hi it works, but another problem surfaces when i try to delete the spreadsheet with the Reset Button. This was cause by Sheets(2) deleted? Is there anyway i can delete both sheets together to prevent this error? Thanks


    Code
    1. Application.DisplayAlerts = False
    2. Sheets(2).Delete
    3. Sheets(3).Delete
    4. Application.DisplayAlerts = True
  • Re: Macro To Import Data & Compare


    Haha I've solve it its as simple as


    Sheets(3).Delete
    Sheets(2).Delete


    But can i ask you is it possible for me that the flow of copying spreadsheet to Sheet(2) and (3) be controlled such that when the first import button is pressed its at sheets(2) and the second import button is pressed the sheet is at sheets(3). I know my explanation might be quite bad..


  • Re: Macro To Import Data & Compare


    Instead of this

    Code
    1. Worksheets(1).Copy after:=Wkb1.Worksheets(1)


    Try this

    Code
    1. Worksheets(1).Copy after:=Wkb1.Worksheets(Wkb1.Sheets.Count)


    NOTE: It might be Count -1. Just give both a try

  • Re: Macro To Import Data & Compare


    Hi dbrown14,


    Thanks for your help,

    Question 1: But i have a query yet again why are my comparison module (Module1) which handles the differentiating of part number at column C does not work anymore? Did i happen to disable anything that i shouldn't? With puzzling result when i press Run comparison every single row gets highlighted red instead of the real differences between both sheets?


    Question 2: Is there a possibility that i could set my various button to my desired with my below sub module in (Module1) as it kept giving me error because of undefined buttons called here.


    Code
    1. Sub SetDefaults() CheckBoolean1 = False CheckBoolean2 = False'--------------------------------------------------------'Application.DisplayAlerts = False'With ActiveWorkbook 'If (.Sheets.Count) >= 3 Then .Sheets(3).Delete 'If (.Sheets.Count) >= 2 Then .Sheets(2).Delete'End With'Application.DisplayAlerts = TrueImportButton1.Enable = TrueImportButton2.Enable = FalseComparisonButton.Enable = FalseResetButton.Enable = False'--------------------------------------------------------End Sub


    With my latest attachment below. Thanks for your time and help [Blocked Image: http://www.mrexcel.com/forum/images/smilies/icon_smile.gif


    Regards,
    imso

  • Re: Macro To Import Data & Compare


    1) Giving the exact error could be very helpful in finding the problem in a reasonable manner


    2) Maybe move the differences subroutine into the worksheet coding space with the buttons. Then you can declare it public which makes your code safer from misuse.

  • Re: Macro To Import Data & Compare


    I've tried moving the Differences module to the worksheet as you mention but it still doesn't work? When i press the run button almost everything will be highlighted along the rows no matter the differences in part number but with no error massage occurring? Maybe you could take a look at my latest attachment to get what i mean?

  • Re: Macro To Import Data & Compare


    The value you are trying to Find is temp.Value which come out to 019320P-005 and since you are using xlWhole as a matching type it does not match what's actually in the cell which is ="019320P-005".


    One solution change ="019320P-005" to just 019320P-005 in your sheets. Another is to do this for the find string. "=""" & temp.value & """"


    Also I noticed that when you do your find functions you refer back to the same range the temp cell is coming from. (e.g. temp comes from rng2 then you do rng2.find(temp) which will highlight all cells)

  • Re: Macro To Import Data & Compare


    Quote

    Another is to do this for the find string. "=""" & temp.value & """"


    Sorry for not understanding this sentence of yours above? How do you really implement this sentence into the codes?

  • Re: Macro To Import Data & Compare


    Just confirm is that what you meant below but why is it still not working?