VBA Help - Add Column, Formula to subtract surrounding columns

[CodingMonkey]

Hey all,

I've always had wonderful luck on this website with the wealth of knowledge available here.

My spreadsheet contains columns that contain the current weekly inventory. This adds a column every week, making the table dynamic and ever-expanding.

Example of last two columns:
| Pint Stock MM/DD/YY | Pint Stock MM/DD/YY |
______________92______________________109___________


I've ran into a wall as to how to code, if even possible, the following situation:

I want to add a column to left of last column (we will call this "new column") -> insert a formula that will subtract column on right of "new column" by column on left of "new column" then autofill through the rows below.


Example of last three columns (with click of button)

| Pint Stock MM/DD/YY | "New Column" | Pint Stock MM/DD/YY |
____________92___________________17_______________________109__________



Is something like this even possible in a dynamic or rolling situation as my spreadsheet is?

 

[dooniem]

Can you dream it, Excel can do it.
Since you don't post any code here we will have to assume a lot.
You already run a macro from a button to add the current weekly stock, right? So you already have a code that indentifies the column number (or letter) of the last column?

For your main task you have 2 options.
Either add the "New Column" before (1) or after adding the newest "Pint Stock" column (2). Doesn't really matter, just need to decide.
But you seem interested in adding a column in between two columns...Depending on what column you have as reference:

'You'll probably want to switch ActiveCell with Range("YourRange"), the Range where the rightmost column is 
ActiveCell.EntireColumn.Insert 'to the left of activecell (1)
ActiveCell.EntireColumn.Offset(0, 1).Insert 'to the right of active cell (2) [Offset(rows,columns)]

You will now need to add a formula to the "New Column", depending on how you inserted the new column the address/range of that column varies.

.Range("YourRange").Formula = "=$A$4+$A$10" 'Notice the hyphens and starting with an "equals" sign. Enter the same way you would outside of VBA.

To fill down to last row in use, you need to know where to stop.
If you don't have a way to find the last row you want to fill down to, you need to read this:
5 Different Ways to Find The Last Row or Last Column Using VBA — The Spreadsheet Guru
They all have pros and cons.

Filling down a formula is done like this:

Range("M3:M" & LastRow).FillDown
'Or the formula and filldown can be achieved as a oneliner 
Range("M3:M" & LastRow).Formula = "=G3&"",""&L3"

But the "Range code" depends on how you adress the column with the formula. ie:
Range(cells(1,ClumnNumber),Cells(LastRow,ColumnNumber)) 'Where cells is refered to as [row number ,column NUMBER]

Does that help you get started?