Fill right VBA is skipping over a column

[gaudrco]

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:

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...mulas-over-right-into-new-column-added-4.html

 

[gaudrco]

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.

 

[gaudrco]

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.

 

[MARK858]

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.

 

[gaudrco]

It could also be that I separated the code into two pieces. At first, I had this:

[COLOR=#333333]Dim ws As Worksheet, oLo As ListObject[/COLOR]

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 [COLOR=#333333]End With[/COLOR]

Now I have this:

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

 

[NoSparks]

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.