+ Reply to Thread
Results 1 to 6 of 6

VBA to Search for records in a TABLE and Overwrite/ADD new data from a MATCHING ITEM!

  1. #1
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    83

    VBA to Search for records in a TABLE and Overwrite/ADD new data from a MATCHING ITEM!

    Afternoon all,

    I have recently started working with tables in excel and am trying to do alot of things via VBA.

    I have a main database table on one sheet, then an Items table on another sheet. When i update the Items table with new data i want it to find all the records from the main database table and update their records, either overwrite all data or add new data depending on which you select from a drop down.

    ive produced this:

    Please Login or Register  to view this content.

    Im yet to add the add data part but it does do basically what i want.


    My question is: How can i make this more flexible, ie if i move columns around on the items table or add new columns, how can i make the VBA recognise that as opposed to hard coding in all the columns. I was thinking some kind of ARRAY that builds a list of headers, then once a record is found it matches the header in one table to the array and adds the data??


    Is this at all possible, any assistance would be greatly appreciated

    If attached an example of my file, which has other bits ive been working on.Asset Register.xlsm

    Many Thanks

    James

  2. #2
    Registered User
    Join Date
    03-08-2013
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013 ProPlus
    Posts
    7

    Re: VBA to Search for records in a TABLE and Overwrite/ADD new data from a MATCHING ITEM!

    Hi there,

    A couple things about your code before we get into the nuts and bolts of it. In your declared variables you have this line...

    Please Login or Register  to view this content.
    Only the second item is declared as a ListObject, while the first is a Variant type which will be set when it's first used. All variables should be independently declared. So to declare them both as ListObject object types, it would need to be this...

    Please Login or Register  to view this content.
    This holds true of all your variable declarations.

    Also, you've declared some range variables as your list columns data body range. That makes sense, but then later on you're setting other variables to the same thing. For example, you set the TaItemCol variable to the list column "Item" of the "FFE" table, and then inside your loop you use this line...

    Please Login or Register  to view this content.
    This makes the rngSearch variable completely superfluous.

    Just in briefly looking at your code, I'm not sure it's doing what you want. Your'e looping through every cell in the Items table, not just a single column. From what I can tell, your "Item" field in the Items table are all unique values, and you're wanting to update all rows in the FFE table with those matching "Item" field values from the Items table. Is that right?

    This can be done without looping. I'll work on the code if you can let me know if I'm looking at this correctly.

  3. #3
    Registered User
    Join Date
    03-08-2013
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013 ProPlus
    Posts
    7

    Re: VBA to Search for records in a TABLE and Overwrite/ADD new data from a MATCHING ITEM!

    On second thought, why not just use formulas on the Database sheet? Does this have to be in code?

  4. #4
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    83

    Re: VBA to Search for records in a TABLE and Overwrite/ADD new data from a MATCHING ITEM!

    Hi Zack,

    I want the database to store all of the information, wouldnt use formulas mean that it could move around?

    Thanks for the tips on declaring variables i thought that by doing it my way was declaring them oops!

    Ultimutely what im trying to create is an asset management system, which has various sites, each site has different asset systems, which is then linked to different assets, but some of those assets are the same across different sites.

    Any help would be appreciated, but i think i may be gong about it all wrong.


    Thanks

  5. #5
    Registered User
    Join Date
    03-08-2013
    Location
    Oregon, United States
    MS-Off Ver
    Office 2013 ProPlus
    Posts
    7

    Re: VBA to Search for records in a TABLE and Overwrite/ADD new data from a MATCHING ITEM!

    No problem. I would use formulas here, in your Database sheet on the FFE table in the last three columns. You can use this formula in the "Life Span" column and copy to the right and then fill them down...

    Please Login or Register  to view this content.
    To make life easier, since "Item" is a unique list, I'd set it as a named range and use it with data validation on the FFE table. To do this, hit CTRL+F3 (or go to FORMULAS | Defined Names | Name Manager), click the New button, and for the name you can type "ItemList" or some other unique name (can't be the same name as a Table), and in the Refers To text box enter "=Items[Item]" (w/o quotes of course).

    Next, select your entire Item column in the FFE Table (exclude header and total rows) and go to DATA | Data Tools | Data Validation, choose "List" from the Allow combo box, and in the Source text box enter "=ItemList" (again, w/o quotes). Then each cell will have an in-cell drop down with the values from the Item Table.

    In the end, there are some points which could cause problems down the road, for an asset management system, utilizing Excel this way. There's nothing to say you couldn't enter duplicates in the Items Table. With the above formula nothing but the first unique Item would be picked up or seen. There's no input validation for this data. There are some decent Access templates for this available for free. Not sure if you have Access or not though, and if you'd be up to the slight learning curve. Just a thought.

    HTH

  6. #6
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    83

    Re: VBA to Search for records in a TABLE and Overwrite/ADD new data from a MATCHING ITEM!

    Zack,

    Thanks for this has done what i needed it too, sometimes i look at VBA when i need to learn more formulas!

    Ill have a look at the Access templates see what i can find

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. UserForm: Search Item in List > Call Table associated with Item > Execute Command
    By TexasAggie12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-25-2014, 12:58 PM
  2. Pivot Table Calculated Item - Too Many Records
    By kareni in forum Excel General
    Replies: 3
    Last Post: 06-03-2011, 12:15 PM
  3. insert data search by matching records
    By jjgal767 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-28-2011, 11:20 PM
  4. insert data from child workbook to master workbook by search matching records
    By jjgal767 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-21-2011, 05:10 AM
  5. Adding Search Function To Delete Matching Records
    By sweetrevelation in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-01-2009, 09:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1