using OFFSET to copy formulas to inserted columns

[Tdorman]

Hello,

I created a workbook that automatically add a certain number of columns. I need these new columns to copy the formulas over from the original column. For example, I have a main column that is present in every sheet (the formulas are different in every sheet). This column has formulas in it that will be the same for each new column, however, the references will need to changed as columns are added, i.e. instead of referencing F11:F14 it would need to be G11:G14. I was able to use the OFFSET function for a few of the formulas and that provided the desired result. However, those were pulling from a single cell on a different sheet. The other formulas on this sheet are SUM, PRODUCT, etc. functions that I cant seem to get the OFFSET function to work like the other formulas.

This is the worksheet that i have been playing around with. When I change the value on the main sheet and have columns inserted to this sheet, I need the formulas in Column F to carry across to the new columns. The OFFSET is working for the first new member, but not any others.

Test4.xlsm
	F	G	H
1	Managerial Member	Member2	Member3
2	NU1008976543	NU1008976543	NU1008976543
3	AAAAAAAAA	BBBBBBBBB	CCCCCCCCC
4	111111111	222222222	333333333
5
6
7
8	-7,181,711	-76,340	0
9	0	0	0
10	0	0	0
11	0	0	0
12	-7,181,711	-76,340	-76,340
Sheet3

Cell Formulas
Range	Formula
F2:H2	F2	=DATA!$A$2
F3:H3	F3	=OFFSET(DATA!$C$2,COLUMN()-6,0)
F4:H4	F4	=OFFSET(DATA!$D$2,COLUMN()-6,0)
F8:H8	F8	=OFFSET(DATA!$L$2,COLUMN()-6,0)
G9:H11	G9	=0
F12	F12	=SUM(F8:(OFFSET(F11,-1,0)))
G12	G12	=SUM(OFFSET(F8,0,1):OFFSET(F11,0,1))
H12	H12	=SUM(OFFSET(F8,0,1):OFFSET(F11,0,1))

 

[jorismoerings]

Hi,

Not entire sure what you're trying to achieve without knowing what the other functions are you want to use with OFFSET but i'll give it a go.

Try this and see if this helps you forward with OFFSET on the other fnctions.

Book1
	F	G	H
1	Managerial Member	Member2	Member3
2	NU1008976543	NU1008976543	NU1008976543
3	0	0	0
4	0	0	0
5
6
7
8	0	0	0
9	1	0	0
10	0	2	0
11	0	0	3
12	1	2	3
Sheet1

Cell Formulas
Range	Formula
F2:H2	F2	=data!$A$2
F3:H3	F3	=OFFSET(data!$C$2,COLUMN()-6,0)
F4:H4	F4	=OFFSET(data!$D$2,COLUMN()-6,0)
F8:H8	F8	=OFFSET(data!$L$2,COLUMN()-6,0)
G9,G11,H9:H10	G9	=0
F12:H12	F12	=SUM(OFFSET(F$8,0,0,ROWS(F$8:F11),1))

 

[Tdorman]

Hi @jorismoerings,

Thank you for your response. The issue with the above formulas is that columns G and H will be inserted automatically so, based on the code that I have right now, a generic formula needs to be used because it would be the same formula for each inserted column. The code for row 12 currently is

.Cells(12, i).Value = "=SUM(OFFSET(F8,0,1):OFFSET(F11,0,1))"

I didn't know if there was a formula I could put there that would sum rows 8 to 11 for the automatically inserted columns. I also didnt know if there was a way to have the code pull the formulas from the main column that is always on the sheet.

This is the code in the main sheet

Private Sub Worksheet_Change(ByVal Target As Range)

    Const SOMESHEETS As String = "*Sheet2*Sheet3*"                   ' <<< change / append sheet names to suit
                                                                     '     be sure each sheet name is between * characters
    Dim KeyCells As Range, ColNum As Long
    Dim ws As Worksheet
    
    Set KeyCells = Range("B2")
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        If IsNumeric(KeyCells.Value) Then
            ColNum = KeyCells.Value
            If ColNum > 0 Then
                For Each ws In ThisWorkbook.Worksheets
                    If CBool(InStr(LCase(SOMESHEETS), LCase("*" & ws.Name & "*"))) Then
                        InsertColumnsOnSheet argSheet:=ws, argColNum:=ColNum
                    End If
                Next ws
            End If
        End If
    End If
End Sub

This is the code in the standard module

Option Explicit
Public Sub InsertColumnsOnSheet(ByVal argSheet As Worksheet, ByVal argColNum As Long)

    Dim Rng As Range, c As Range
    Dim TotalCol As Long, LeftFixedCol As Long
    Dim i As Long
    Dim ws As Worksheet

    Set ws = Worksheets("Sheet2")
    With argSheet
        Set Rng = .Range(.Cells(4, 1), .Cells(4, .Columns.Count))
        Set c = Rng.Find("END")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 1
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(4, i).Value = "Member" & i - LeftFixedCol
                    .Cells(5, i).Value = "=DATA!$A$2"
                    .Cells(6, i).Value = "=OFFSET(DATA!$C$2,COLUMN()-2,0)"
                    .Cells(7, i).Value = "=OFFSET(DATA!$D$2,COLUMN()-2,0)"
                    .Cells(8, i).Value = "=OFFSET(DATA!$E$2,COLUMN()-2,0)"
                    .Cells(10, i).Value = "=OFFSET(DATA!$F$2,COLUMN()-2,0)"
                    .Cells(12, i).Value = "=OFFSET(DATA!$G$2,COLUMN()-2,0)"
                    .Cells(13, i).Value = "=OFFSET(DATA!$H$2,COLUMN()-2,0)"
                    .Cells(14, i).Value = "=OFFSET(DATA!$I$2,COLUMN()-2,0)"
                    .Cells(16, i).Value = "=OFFSET(Data!$K$2,COLUMN()-2,0)"
                    .Cells(17, i).Value = "=OFFSET(Data!$J$2,COLUMN()-2,0)"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
    
    Set ws = Worksheets("Sheet3")
    With argSheet
        Set Rng = .Range(.Cells(1, 1), .Cells(1, .Columns.Count))
        Set c = Rng.Find("TOTAL")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 5
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(1, i).Value = "Member" & i - LeftFixedCol
                    .Cells(2, i).Value = "=DATA!$A$2"
                    .Cells(3, i).Value = "=OFFSET(Data!$C$2,COLUMN()-6,0)"
                    .Cells(4, i).Value = "=OFFSET(DATA!$D$2,COLUMN()-6,0)"
                    .Cells(8, i).Value = "=OFFSET(DATA!$L$2,COLUMN()-6,0)"
                    .Cells(9, i).Value = "=0"
                    .Cells(10, i).Value = "=0"
                    .Cells(11, i).Value = "=0"
                    .Cells(12, i).Value = "=SUM(OFFSET(F$8,0,0,ROWS(F$8:F11),1))"
                    .Cells(13, i).Value = "=.25"
                    .Cells(14, i).Value = "=PRODUCT(OFFSET(F13,0,1),$C$12)"
                    .Cells(15, i).Value = "=IF($C$14<=0,0,IF(OFFSET(F14,COLUMN()-6,0)<=OFFSET('500U'!D20,COLUMN()-6,0),OFFSET(F14,COLUMN()-6,0),OFFSET('500U'!D20,COLUMN()-6,0)))"
                    .Cells(16, i).Value = "=IF($C$14<0,0,IF(SUM(OFFSET(F14:F15,COLUMN()-6,0))<0,0,SUM(OFFSET(F14:F15,COLUMN()-6,0))))"
                    .Cells(17, i).Value = "=IF($C$16<0,0,OFFSET('500U'!D37,0,1))"
                    .Cells(18, i).Value = "=IF(SUM(OFFSET(F16:F17,0,1))<0,0,SUM(OFFSET(F16:F17,0,1)))"
                    .Cells(19, i).Value = "=-(MIN(F18,ABS(OFFSET('Sch R'!C82,0,1))))"
                    .Cells(21, i).Value = "=IF(SUM(OFFSET(F18:F20,0,1))<0,0,SUM(OFFSET(F18:F20,0,1)))"
                    .Cells(22, i).Value = "=IF(OFFSET('Sch R'!C45,0,1)>0,OFFSET('Sch R'!C45,0,1),IF(OFFSET('Sch R'!C63,0,1)>0,OFFSET('Sch R'!C63,0,1),0))"
                    .Cells(25, i).Value = "=OFFSET(F21,0,1)+OFFSET(F22,0,1)-OFFSET(F24,0,1)"
                    .Cells(29, i).Value = "=SUM(OFFSET((F25:F28),0,1))"
                    .Cells(30, i).Value = "=IF(OFFSET(F29,0,1)>100000,OFFSET(F29,0,1)*0.09,IF(AND(OFFSET(F29,0,1)<=100000,OFFSET(F29,0,1)>50000),OFFSET(F29,0,1)*0.075,OFFSET(F29,0,1)*0.065))"
                    .Cells(32, i).Value = "=SUM(OFFSET(F30:F31,0,1))"
                    .Cells(33, i).Value = "=SUM(OFFSET(F25:F26,0,1))"
                    .Cells(34, i).Value = "=IF(OFFSET(F33,0,1)>1000000,OFFSET(F33,0,1)*0.025,0)"
                    .Cells(37, i).Value = "=SUM(OFFSET(F35:F36,0,1))"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
End Sub

 

[jorismoerings]

Hi,

The issue with the above formulas is that columns G and H will be inserted automatically so, based on the code that I have right now, a generic formula needs to be used because it would be the same formula for each inserted column.

Inserting the formula into VBA, changes things but not dramatically however in the future, please include all the required conditions for your problem so that it can be addressed directly.

Your VBA part says:

.Cells(12, i).Value = "=SUM(OFFSET(F8,0,1):OFFSET(F11,0,1))"

Change that part to:

.Cells(12, i).FormulaR1C1 = "=SUM(OFFSET(R[-4]C,0,0,4,1))"

I haven't scrutinized the rest of your code so i don't know if it breaks on other parts.

 

[Tdorman]

Thank you for your help switching the format to R1C1 worked, editing all the formulas now. Thanks again