VBA insert n new columns and calculate difference of previous n-2 columns

Bering

Board Regular
Joined
Aug 22, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I have a workbook with 2 worksheets: in sheet1, column B, users enter a list of items (the number of item can vary but the last item is always called Total).
For each item in that list, a macro adds a corresponding column in sheet2 (headers = item) plus an additional one, unrelated to the list. The macro will also insert some formulae in each new column in sheet2 (row 6 to 53).

Example: in sheet2 there are 20 columns, the user enters 4 items in sheet1, the macro will create 5 additional columns, hence 25 in total.
For the last item in the list in sheet1, which is always called Total, I would like to add in the corresponding column in sheet2, a formula that calculates the difference of the n- 2 previous columns (3 in this example): item Total would correspond to Column 24 and I would like to populate the range row 6 to 53 with a formula: Column 21 - Column 22 - Column 23

I am not sure how I could achieve this.

Many thanks in advance
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Assuming the items on sheet1 start in cell B2 and the headings on sheet2 are in row 5, try this:

VBA Code:
Sub insert_new_columns()
  Dim f As Range, lc As Long, n As Long
  Dim sh1 As Worksheet, sh2 As Worksheet
  
  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")
  
  Set f = sh1.Range("B:B").Find("Total", , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    With sh1.Range("B2", f)
      .Copy
      n = .Rows.Count - 1
    End With
    If n > 0 Then
      lc = sh2.Cells(5, Columns.Count).End(1).Column + 1
      sh2.Cells(5, lc).PasteSpecial xlPasteValues, , , True
      With sh2.Cells(6, lc + n)
        If n = 1 Then
          .Formula = "=" & .Offset(0, -n).Address(0, 0)
        Else
          .Formula = "=" & .Offset(0, -n).Address(0, 0) & "-SUM(" & .Offset(0, -n + 1).Address(0, 0) & ":" & .Offset(0, -1).Address(0, 0) & ")"
        End If
        .Copy sh2.Range(sh2.Cells(6, lc + n), sh2.Cells(53, lc + n))
      End With
    Else
      MsgBox "No items"
    End If
  Else
    MsgBox "The item 'Total' does not exist"
  End If
End Sub
 

Bering

Board Regular
Joined
Aug 22, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
Thank you sooo much Dante, this works perfectly (y) (y) (y)


Assuming the items on sheet1 start in cell B2 and the headings on sheet2 are in row 5, try this:

VBA Code:
Sub insert_new_columns()
  Dim f As Range, lc As Long, n As Long
  Dim sh1 As Worksheet, sh2 As Worksheet

  Set sh1 = Sheets("Sheet1")
  Set sh2 = Sheets("Sheet2")

  Set f = sh1.Range("B:B").Find("Total", , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    With sh1.Range("B2", f)
      .Copy
      n = .Rows.Count - 1
    End With
    If n > 0 Then
      lc = sh2.Cells(5, Columns.Count).End(1).Column + 1
      sh2.Cells(5, lc).PasteSpecial xlPasteValues, , , True
      With sh2.Cells(6, lc + n)
        If n = 1 Then
          .Formula = "=" & .Offset(0, -n).Address(0, 0)
        Else
          .Formula = "=" & .Offset(0, -n).Address(0, 0) & "-SUM(" & .Offset(0, -n + 1).Address(0, 0) & ":" & .Offset(0, -1).Address(0, 0) & ")"
        End If
        .Copy sh2.Range(sh2.Cells(6, lc + n), sh2.Cells(53, lc + n))
      End With
    Else
      MsgBox "No items"
    End If
  Else
    MsgBox "The item 'Total' does not exist"
  End If
End Sub
 
Last edited by a moderator:

Bering

Board Regular
Joined
Aug 22, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
Hello Dante,

Just a question if you don't mind: suppose I need to insert the formula =P*$E in range 5 - last row in column P; How would that translate using the notation Offset().Address() in your code above?

Many thanks
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

P*E in P would produce a circular reference error.
It would always be P*E, because it may be that P is empty. I don't quite understand what it would be like, since the columns are dynamic, it depends on the number of items, so P could be empty or with an item or with "Total"
 

Bering

Board Regular
Joined
Aug 22, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
Apologies, I meant to say P$4*E (I have an amount in row P4 that I need to allocate by a ratio in column E from row 5 to last row)

P*E in P would produce a circular reference error.
It would always be P*E, because it may be that P is empty. I don't quite understand what it would be like, since the columns are dynamic, it depends on the number of items, so P could be empty or with an item or with "Total"
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Sorry, but I still don't understand. This is very clear to you because you are seeing it and you know your data.
It would help if you put an image explaining where you want the formula.
 

Bering

Board Regular
Joined
Aug 22, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
Example.JPG



I hope this clarifies, thank you
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Always in column P? Or in the first column where the macro begins to put items?
Only in the P or also in the Q and in the R?
 

Bering

Board Regular
Joined
Aug 22, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
all the columns created by the macro actually, so yes P, Q and R in this example.

Many thanks




Always in column P? Or in the first column where the macro begins to put items?
Only in the P or also in the Q and in the R?
 

Watch MrExcel Video

Forum statistics

Threads
1,112,768
Messages
5,542,427
Members
410,550
Latest member
ganeshsamant
Top