Macro To Import Data & Compare

  • Re: Macro To Import Data & Compare


    Look at you Find functions inside the for loop. You are looking for temp taken from rng2 and searching for it in rng2. In other words, you'll always get a match. You have to change rng2.Find(...) to rng3.Find(...) and do the same for the next for loop

  • Re: Macro To Import Data & Compare


    Haha how could i have missed that, now it works :) Thanks


    About my question 2 Any ideas how i can initially set the various button when open the workbook?

    Quote

    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.


    ThisWorkBook Module

    Code
    1. Private Sub Workbook_Open()SetDefaultsEnd Sub


    Module1 codes


    Regards,
    imso

  • Re: Macro To Import Data & Compare


    Sorry for my bad explaination if it somehow confuse you in a way. What i meant was i wanted to preset every single of those buttons seen in the Sheets(1) to always be in a certain manner with .Enable in Module1 and this sub module will be called upon by the ThisWorkBook whenever the excel file is first open. But i couldn't because i always encounter the Buttons not declared. Any work around for this problem? Thanks :)



    ThisWorkBook

    Code
    1. Private Sub Workbook_Open()
    2. SetDefaults
    3. End Sub
  • 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


    Put SetDefaults into the same coding space as the open event and do this

    Code
    1. Sheets("YourSheetNameHere").YourObjectHere.Property = Whatever
    2. 'For example
    3. Sheets("Sheet1").ResetButton.Enabled = False
  • Re: Macro To Import Data & Compare


    Hi dbrown14,


    I've finishing my assignment soon. Thank for your time and effort all these while for helping me to answer my questions. Really appreciate it! :)


    Best Regards,
    imso

  • Re: Macro To Import Data & Compare


    Hi dbrown14,


    Sorry to bring up the topic again but I've kinda been recommended to add improvements. Which i am quite puzzled on how to implement it.


    Brief Description:


    After running the differences test I'm required to:
    1) Create a new sheet with Sheets.Add
    2) Populate the differences of both sheets(2) and (3) into this new sheet via copy/paste onto the new sheet in two sections. Something like a summary chart. (All the Color formats all what so ever formats remain unchanged.)


    Maybe you could look at my attachment below which i did a rough manual demo of what i mean. Thanks for your time :)


    Regards,
    imso

  • Re: Macro To Import Data & Compare


    Use this in place of your differences subroutine

  • Re: Macro To Import Data & Compare


    Hi dbrown14,


    Thanks for answering. :) I've try added some change to my code so as to add Sheets(4) but it seems like it kept staying at the Sheets(2) position?



    And some change to your above code. Is it ok?

    Code
    1. Set CompareSht = Sheets(4)


    And when i run your code i kept bump into this error from this line. With error message run time error '1004'??

    Code
    1. Range("A2").Resize(j, UBound(a, 2)).Value = a


    Regards,
    imso

  • Re: Macro To Import Data & Compare


    Maybe this will be better. It will add the sheet for you now

  • Re: Macro To Import Data & Compare


    Hi dbrown14,


    Thanks it works fine now after your precious recommendations. :)


    Sorry to trouble you again with a whole new set of problem. I wanted to ask you is Excel-vba able to allow me to drag and drop items in a inventory list from drawers to drawers and at the same keeping track of the quantity while items are being drop around?


    Maybe you might know what i mean after looking at my attachment file below but i don't know is there such an api in excel. If there is how is it done? Thanks for your time

  • Re: Macro To Import Data & Compare


    Hi dbrown14,


    I think you're should take a look at my attachment above as this is a whole new format of what i'm asking previously. Pertaining about adding drag and drop functionality using excel-vba in order to keep track of the quantity of item in the inventor list.

  • Re: Macro To Import Data & Compare


    Sorry for the late reply, trying to think how am i going to explain to you.. But if you still do not understand maybe i will get back to you next week Monday. :)


    Its drag and drop somehow, but its through drag and drop of the items from one drawer to the other (drawers) to help keep track of a item quantity. For example, a lab user can move certain items from drawers to drawers. So items might decrease or increase depending on how many the lab user have transfer the item to. Each item transfer to another drawer will create a item if it exist a not. So this excel application allow the user to keep track the inventory without much user typing or what so ever.


    This is something like a user friendly "real time inventory tracker".


    Sorry if my explanation is kinda hard to understand. :(

  • Re: Macro To Import Data & Compare


    Example:
    Drawer 1 --> 4 pencil
    Drawer 2 --> Might not really exist a single pencil in the drawer OR already have existing 2 pencil


    Now the user decides to move 3 pencil into drawer 2 (Real life)
    Drawer 2 as stated above might be empty without the existence of the item named pencil OR already have existing 2 pencil


    Now the user go back to excel (which is this inventory list application) and drag and drop the pencil seen in drawer 1 with the cursor. With each drag decrements drawer 1 pencil by 1 and drop increment drawer 2 pencil by 1 to the existing pencil if it exist in the drawers. In the end the the user have to drag and drop it to the destination (drawer 2) 3 time to achieve the real value of 5 pencil in drawer 2 if presumably it have 2 pencil previously.