Results 1 to 6 of 6

Thread: Fill right VBA is skipping over a column
Thanks Thanks: 0 Likes Likes: 0

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

    Default Fill right VBA is skipping over a column

    I have a command button that adds a column to a table. The table has formulas that need to be filled to the right with each new column added. Here is the vba that I have been using to achieve this:

    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("C57")
            ws.Range(.ListColumns(.ListColumns.Count - 1).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight
        End With
    End With

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


    Here are the reference column formulas:
    =IFERROR(INDEX('Competitor Comparison Data'!T:T,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")
    =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")


    This problem arose from this thread: https://www.mrexcel.com/forum/excel-...n-added-4.html

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

    Default Re: Fill right VBA is skipping over a column

    I don't think this is a vba issue but perhaps if the vba code was altered to accomplish the same thing, then maybe the fill would work properly.

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

    Default Re: Fill right VBA is skipping over a column

    I think I figured it out and I will share what I did in case anyone runs into a similar problem.

    My actual code is much larger than the one I shared above. The way I solved this issue was by moving the line of code (that fills the column to the right) farther down in my code. By doing this, I believe it gave Excel more time to process the adjustment of adding a column.

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,109
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Fill right VBA is skipping over a column

    By doing this, I believe it gave Excel more time to process the adjustment of adding a column.
    I can't see how that would be the reason, much more likely there is something in the code that you have moved it below that affected it.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

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

    Default Re: Fill right VBA is skipping over a column

    It could also be that I separated the code into two pieces. At first, I had this:
    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("C57")
            ws.Range(.ListColumns(.ListColumns.Count - 1).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight
        End With End With
    Now I have this:
    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("C57")
        End With
    End With
    
    'Other code in between here, including a msgbox
    
    Set ws = Sheets("Competitor Comparison")
    With ws
        Set oLo = .ListObjects("CompComparisonTable")
        With oLo
    ws.Range(.ListColumns(.ListColumns.Count - 1).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight
        End With
    End With

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

    Default Re: Fill right VBA is skipping over a column

    By doing this, I believe it gave Excel more time to process the adjustment of adding a column.
    To see if was a timing thing with your original "Competitor Comparison" sheet, I added a DoEvents, and a 5 second wait to the macro before doing the auto fill and it made no difference.

    I even wrote the actual r1c1 formulas to the new column instead of auto filling and Excel still changed the formulas that should have been W:W to X:X.

    "Other code in between here, including a msgbox"
    Why going to a different sheet for a couple of manipulations, then returning to "Competitor Comparison" to auto fill the formulas is working is beyond me.

    Anyway, glad you're now happy.
    Good luck with your project.

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
  •