Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 37

Thread: Filling formulas over to the right into a new column added

  1. #21
    Board Regular
    Join Date
    Mar 2013
    Posts
    802
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    What's wrong with the code in post 16 ?

  2. #22
    Board Regular
    Join Date
    Aug 2019
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    Hmm.. Very odd; that code is working for me now. I wonder why I couldn't get it to work the first time I tried. I apologize for dragging this thread out for longer than it needed to be. I really appreciate your help

    Thanks!

  3. #23
    Board Regular
    Join Date
    Aug 2019
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    Scratch that.

    The fill command is not behaving correctly. When I click the button to add a new column, the new column does not fill the correct column starting point.

    Here is the reference column formula:
    =IFERROR(INDEX('Competitor Comparison Data'!U:U,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")

    Here is the new column formula that is filled into the new column when the button is clicked:
    =IFERROR(INDEX('Competitor Comparison Data'!W:W,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")

    So it skipped over V:V and went to W:W. Additionally, when I add a second column, the first added column changes from W:W to X:X.

    Once I add a column, formulas in existing columns should not change.

    So two issues.
    1. The first column added is not starting with the correct reference column; its skipping over V:V
    2. Every new column added changes the reference column of all the other added columns. If I added 3 columns, the order of my last 4 columns would be as follows:

    =IFERROR(INDEX('Competitor Comparison Data'!U:U,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match") Correct Column
    =IFERROR(INDEX('Competitor Comparison Data'!Y:Y,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")
    =IFERROR(INDEX('Competitor Comparison Data'!Z:Z,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")
    =IFERROR(INDEX('Competitor Comparison Data'!AA:AA,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")

  4. #24
    Board Regular
    Join Date
    Aug 2019
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    Quote Originally Posted by NoSparks View Post
    how about
    Code:
        Dim ws As Worksheet, oLo As ListObject
    
    Set ws = Sheets("Competitor Comparison")
    With ws
        Set oLo = .ListObjects("CompComparisonTable")
        With oLo
            .ListColumns.Add
            oLo.HeaderRowRange.Cells(1, .ListColumns.Count).Value = Range("C2")
            ws.Range(.ListColumns(.ListColumns.Count - 1).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight
        End With
    End With
    I changed
    Code:
    ws.Range(.ListColumns(.ListColumns.Count - 1).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight
    To
    Code:
    ws.Range(.ListColumns(.ListColumns.Count - 2).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight
    That seems to have fixed the problem

  5. #25
    Board Regular
    Join Date
    Mar 2013
    Posts
    802
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    It's not the VBA.
    Has to do with deleting columns, which you are likely doing lots in the testing process, and Excel automatically adjusting your formulas to account for it.
    The file I posted over at that other forum had the same issue and I would have told you about it had you ever acknowledge my post there.

  6. #26
    Board Regular
    Join Date
    Aug 2019
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    I'm sorry for not replying to your post on the other forum. You have been extraordinary with all your help and I can't thank you enough. Much appreciated.

    Yeah changing the vba did not fix it. I thought it did because it was working. The problem occurs randomly. So when would this work normally? How long after deleting columns would the macro work? Is there a fix?

    Thanks again

  7. #27
    Board Regular
    Join Date
    Mar 2013
    Posts
    802
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    Delete a few entire columns from somewhere to the right of the table back to the last column with correct formulas.

  8. #28
    Board Regular
    Join Date
    Mar 2013
    Posts
    802
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    From the linked page in post 6
    Fills right from the leftmost cell or cells in the specified range. The contents and formatting of the cell or cells in the leftmost column of a range are copied into the rest of the columns in the range.
    Your change in post 24 didn't go back far enough in the table for the start of the range.

  9. #29
    Board Regular
    Join Date
    Aug 2019
    Posts
    139
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    Quote Originally Posted by NoSparks View Post

    Your change in post 24 didn't go back far enough in the table for the start of the range.
    Okay so I deleted entire columns from the end of my table all the way to the end of excels available columns and that did not work. It still skipped over the column in the formula when I added a new column.
    Then I tried to adjust the vba to go back far enough in my table for the start of the range, which equals ( - 17), so the new line of code was:

    Code:
    ws.Range(.ListColumns(.ListColumns.Count - 17).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight
    This did not fix it either. The last column in the formula I have is V:V and the new added column still goes to X:X.

    Did I miss something?

  10. #30
    Board Regular
    Join Date
    Mar 2013
    Posts
    802
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filling formulas over to the right into a new column added

    Not guessing at anything any more.
    Your choice whether to share the workbook or not.
    See post 12.

    if you don't want others to have the workbook, pm the link
    Last edited by NoSparks; Sep 11th, 2019 at 09:24 AM.

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
  •