+ Reply to Thread
Results 1 to 3 of 3

VBA loop each cell with two tables.

  1. #1
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    VBA loop each cell with two tables.

    Hello Experts,

    I am using two tables to keep track of changing data. The original data is in Table2. Table1 get's it's data from table2 by simple reference (each cell references it's counterpart (same position) in table2 i.e.: Cell F6 has formula "= AO6", Cell G7 has "= AP7"etc..). Both tables are the same size. Table1 is in cells G6:W42 and table2 is cells AO6:BF42. These are just ranges (not Excel tables) but could be converted if necessary. Basically I am trying to make it where the user may change a cell in Table1 or hit delete to revert back to the original data from table2. I have vba code to do this one cell at a time (see below) but my table is large. Is there any way to code this with a Loop where I don't have to write this code for each individual cell? Also, how do I handle this if someone adds a row to the tables. I tried to search for this but the only thing I could find was how to use an if statement to put a static value in a table. I'm a vba novice and struggling to figure out how to reference rows and columns in two different tables.

    Please Login or Register  to view this content.
    Thanks for your help!
    MLS
    Attached Files Attached Files
    Last edited by Marvelous; 10-10-2019 at 07:28 AM. Reason: Added example file

  2. #2
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    Re: VBA loop each cell with two tables.

    Bump - No response. Here is my attempt at writing this code but the first line is highlighted and I'm not sure what is wrong.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-13-2013
    Location
    Wichita,KS
    MS-Off Ver
    365 Aps for Enterprise
    Posts
    60

    VBA "Intersect" code causes Excel to crash when inserting rows

    Please help! I have attached the latest file with the updated code. Everything works except when the user tries to insert a row, then excel crashes. I believe Excel is crashing because there is no value in the Offset area in the row that was just inserted. How do I stop this?

    twotables.png

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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. The fastest way to loop in 2 tables
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-20-2019, 11:08 AM
  2. Is it possible to Loop through Tables and Copy TableHeader ?
    By nordicdust in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-28-2018, 07:00 PM
  3. [SOLVED] Making Excel Table Loop to Work in All Tables
    By loverfellow in forum Excel General
    Replies: 4
    Last Post: 12-01-2015, 10:19 AM
  4. Copy paste loop using multiple tables
    By Goldbadger in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-19-2015, 08:36 AM
  5. Loop through tables
    By worldsphere in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-16-2011, 05:42 PM
  6. Using Hlookup on multiple tables with for..next loop
    By Cerberus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-30-2010, 04:52 PM
  7. Code loop through two tables checking and altering
    By excel_lover in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 11-14-2008, 05:50 PM

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