Table Range.Cell formula the same for the entire column

[Gordon7322]

I have a loop that goes through data on another sheet to insert into a table. The rest of the code where I add the value to each cell is fine, but the issue happens when every time I insert a new row into the table, the formula I add to that cell affects every cell in the column, not just the row and column.

The weird thing is that I had a table with values and the code worked fine, every row had its own formula. But when I removed all the rows from the table and tested this again, this time the entire column is connected to 1 formula.

I think I'm overlooking something involving adding to null table values or how I coded with the cell: tableNewRow.Range.Cells(1, 6).Formula

How do I make it so the formula is different for each row? I appreciate any help, thanks.

Dim tableNewRow As ListRow
Dim data As Variant
Dim dayString As String

For i = 1 To UBound(data)
        dicKeyString = data(i, 2)
        If dicKeyString Like "### ### ###" Then
            If dicRecDate.Exists(dicKeyString) Then
                ws.Range("A" & i, "N" & i).Interior.ColorIndex = 46 'orange
                dupRecCount = dupRecCount + 1
            Else
                Set tableNewRow = mainws.ListObjects("Table1").ListRows.Add(AlwaysInsert:=True)   'add new row to table 1
                'Table Col A
                tableNewRow.Range.Cells(1, 1).Value = data(i, 2)
                'Table Col B
                tableNewRow.Range.Cells(1, 2).Value = data(i, 4)
                'Table Col C
                tableNewRow.Range.Cells(1, 3).Value = data(i, 5) 'ws.Range("E" & i).Value
                'Table Col D
                tableNewRow.Range.Cells(1, 4).Value = data(i, 7) 'ws.Range("G" & i).Value
                'Table Col E
                tableNewRow.Range.Cells(1, 5).Value = data(i, 8) 'ws.Range("H" & i).Value
                'Table Col F
                dayString = Left(data(i, 1), 10)
                'set formula
                tableNewRow.Range.Cells(1, 6).Formula = "=DATEDIF(DATEVALUE(" & Chr(34) & dayString & Chr(34) & "), TODAY(), " & Chr(34) & "D" & Chr(34) & ")"  'days in cust"
                ws.Range("A" & i, "N" & i).Interior.ColorIndex = 43 'Green
                addRecCount = addRecCount + 1
            End If
        End If
    Next i

 

[Zack Barresse]

Hi there, and welcome to the board!

First of all, you've done nothing wrong. This is completely expected and normal behavior. Albeit a little confusing at first, I took a stab at explaining it in a blog post here (topic needed to be covered anyway). Basically Excel is adding a calculated column with a fresh, or clean, column. If you have different values in a column (e.g. adding text to any cell in a column prior to adding a formula), then it becomes dirty and Excel won't add the formula as a calculated column.

 

[Gordon7322]

Hi there, and welcome to the board!

First of all, you've done nothing wrong. This is completely expected and normal behavior. Albeit a little confusing at first, I took a stab at explaining it in a blog post here (topic needed to be covered anyway). Basically Excel is adding a calculated column with a fresh, or clean, column. If you have different values in a column (e.g. adding text to any cell in a column prior to adding a formula), then it becomes dirty and Excel won't add the formula as a calculated column.

Awesome blog and very informative post, thank you, it worked!

 

[Zack Barresse]

Very welcome! This is the tidbit I usually try to leave people with: formulas in Tables are always inherently going to want to become calculated columns, and while it's relatively easy to have different formulas in different rows of a Table column (e.g. non-calculated column), there will always be a propensity to overwrite that data mistakenly (just takes one button click and *poof*). If you require different formulas for different rows, I usually don't recommend using a Table. If you're after the formatting, you can always make it a Table, then Convert to Range, and the formatting will stay.