Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Table Range.Cell formula the same for the entire column

  1. #1
    New Member
    Join Date
    Jun 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Table Range.Cell formula the same for the entire column

    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.

    Code:
    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

  2. #2
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table Range.Cell formula the same for the entire column

    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.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

  3. #3
    New Member
    Join Date
    Jun 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table Range.Cell formula the same for the entire column

    Quote Originally Posted by Zack Barresse View Post
    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!

  4. #4
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Table Range.Cell formula the same for the entire column

    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.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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