Filling formulas over to the right into a new column added

gaudrco

Board Regular
Joined
Aug 16, 2019
Messages
203
I have a code for a button that creates a new column at theend of a table. After the new column is added, I would like to have theformulas from the previous table column to fill into the new column. The datain the table should be the only formulas filling to the right (table headersshould not fill, just the data from the table). Here is the code for adding the newcolumn:

Code:
With Sheets("Competitor Comparison").ListObjects("CompComparisonTable")
    .ListColumns.Add(.ListColumns.Count + 1).Name = Range("C2")
    .ListColumns(.ListColumns.Count - 1).Range.EntireColumn.Copy
    .ListColumns(.ListColumns.Count).Range.EntireColumn.PasteSpecial Paste:=xlPasteFormats
End With


Please and thank you
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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!
 
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Delete a few entire columns from somewhere to the right of the table back to the last column with correct formulas.
 
Upvote 0
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.
 
Upvote 0
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:
[COLOR=#333333]ws.Range(.ListColumns(.ListColumns.Count - 17).DataBodyRange, .ListColumns(.ListColumns.Count).DataBodyRange).FillRight[/COLOR]

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?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top