VBA loop each cell with two tables.

**Marvelous** · 10-07-2019, 04:06 PM

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.

'If Range("F6").Value = "" Then
    'Range("F6").Formula = "=AO6"
    'End If
'End Sub

Thanks for your help!
MLS

**Marvelous** · 10-09-2019, 04:31 PM

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.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


'if cells are empty reset to default formulas
'On Error Resume Next
Set myRange = UpdateTable
Set c = Cells.Value

For Each c In myRange
'find value in other table
'myRange

'If c.Value = "" Then
c.Offset(0, 35).Value

    End If

End Sub

**Marvelous** · 11-20-2019, 12:48 PM

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

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
    Dim xSht As Worksheet
    Set xSht = ActiveWorkbook.ActiveSheet
    Dim xRg As Range, xCell As Range
    Set xRg = xSht.Range("DevTable")
    If Not Intersect(Target, Range("DevTable")) Is Nothing Then
        For Each xCell In xRg
            If xCell = "" Then
                xCell.Value = xCell.Offset(0, 35).Value
            End If
        Next xCell
    End If
End Sub