I am trying to get a formula in my code to work that is in R1C1. There are a few conditions within the formula and it is presenting a couple issues transitioning it from A1 to R1C1. The formula is
The main condition is that is a cell in the DATA sheet has an x and a value in the active sheet is greater than 0 it will need to add row 32 and 34, as long as they are above 2000. If 32 and 34 are below 2000, and the cell in the DATA sheet has an X, then it needs to stay at 2000. The first issue is that having an X in the code is not working. I get an error when trying to convert straight to R1C1 because of the x. I am not sure how to handle inputting an X correctly to get the formula to work.
Second, the cells in the data sheet that the formula is pulling increase by row. So the value for each row will increase J2, J3, J4, and so on. The active sheet will go across columns. The current code does not increase the row, presumably because it is not pulling the formula down rows, rather across columns.
VBA Code:
.Cells(35, i).FormulaR1C1 = "=IF(AND(OFFSET(R[-31]C,0,0)>0,OFFSET(DATA!R[-32]C10,0,0)=x),IF(OFFSET(R[-3]C,0,0)>2000,OFFSET(R[-3]C,0,0)+OFFSET(R[-1]C,0,0),2000))"
The main condition is that is a cell in the DATA sheet has an x and a value in the active sheet is greater than 0 it will need to add row 32 and 34, as long as they are above 2000. If 32 and 34 are below 2000, and the cell in the DATA sheet has an X, then it needs to stay at 2000. The first issue is that having an X in the code is not working. I get an error when trying to convert straight to R1C1 because of the x. I am not sure how to handle inputting an X correctly to get the formula to work.
Second, the cells in the data sheet that the formula is pulling increase by row. So the value for each row will increase J2, J3, J4, and so on. The active sheet will go across columns. The current code does not increase the row, presumably because it is not pulling the formula down rows, rather across columns.
Test4.xlsm | ||||
---|---|---|---|---|
F | G | |||
1 | Managerial Member | Member2 | ||
2 | NU1008976543 | NU1008976543 | ||
3 | AAAAAAAAA | BBBBBBBBB | ||
4 | 111111111 | 222222222 | ||
5 | ||||
6 | ||||
7 | ||||
8 | -7,181,711 | -76,340 | ||
9 | 0 | 0 | ||
10 | 0 | 0 | ||
11 | 0 | 0 | ||
12 | -7,181,711 | -76,340 | ||
13 | 1.000000 | 0.250000 | ||
14 | -7,258,051 | -1,814,513 | ||
15 | 0 | 0 | ||
16 | 0 | 0 | ||
17 | 0 | 0 | ||
18 | 0 | 0 | ||
19 | 0 | 0 | ||
20 | 0 | 0 | ||
21 | 0 | 0 | ||
22 | 0 | 0 | ||
23 | 0 | 0 | ||
24 | 0 | 0 | ||
25 | 0 | 0 | ||
26 | 0 | 0 | ||
27 | 0 | 0 | ||
28 | 0 | 0 | ||
29 | 0 | 0 | ||
30 | 0 | 0 | ||
31 | 0 | 0 | ||
32 | 0 | 0 | ||
33 | 0 | 0 | ||
34 | 0 | 0 | ||
35 | 2,000 | #NAME? | ||
36 | 0 | 0 | ||
37 | 2,000 | #NAME? | ||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:G2 | F2 | =DATA!$A$2 |
F3:G3 | F3 | =OFFSET(DATA!$C$2,COLUMN()-6,0) |
F4:G4 | F4 | =OFFSET(DATA!$D$2,COLUMN()-6,0) |
F8:G8 | F8 | =OFFSET(DATA!$L$2,COLUMN()-6,0) |
F12,F29:G29 | F12 | =SUM(F8:(OFFSET(F11,0,0))) |
F14:G14 | F14 | =PRODUCT(OFFSET(F13,0,0),$C$12) |
F15:G15 | F15 | =IF($C$14<=0,0,IF(OFFSET(F14,0,0)<=OFFSET(Sheet4!E20,0,0),OFFSET(F14,0,0),OFFSET(Sheet4!E20,0,0))) |
F16:G16 | F16 | =IF($C$14<0,0,IF(SUM(OFFSET(F14:F15,0,0))<0,0,SUM(OFFSET(F14:F15,0,0)))) |
F17:G17 | F17 | =IF($C$16<0,0,OFFSET(Sheet4!E37,0,0)) |
F18:G18 | F18 | =IF(SUM(OFFSET(F16:F17,0,0))<0,0,SUM(OFFSET(F16:F17,0,0))) |
F19:G19 | F19 | =-(MIN(F18,ABS(IF(OFFSET(Sheet5!D23,0,0)>0,OFFSET(Sheet5!D23,0,0),IF(OFFSET(Sheet5!D82,0,0)>0,OFFSET(Sheet5!D82,0,0),0))))) |
F21:G21 | F21 | =IF(SUM(OFFSET(F18:F20,0,0))<0,0,SUM(OFFSET(F18:F20,0,0))) |
F22 | F22 | =IF(OFFSET(Sheet5!D45,0,0)>0,OFFSET(Sheet5!D45,0,0),IF(OFFSET(Sheet5!D63,0,0)>0,OFFSET(Sheet5!D63,0,0),0)) |
F24 | F24 | =IF(SUM(OFFSET(F21:F22,0,0))<=0,0,PRODUCT(OFFSET(F13,0,0)*OFFSET(F23,0,0))) |
F25:G25 | F25 | =OFFSET(F21,0,0)+OFFSET(F22,0,0)-OFFSET(F24,0,0) |
F30:G30 | F30 | =IF(OFFSET(F29,0,0)>100000,OFFSET(F29,0,0)*0.09,IF(AND(OFFSET(F29,0,0)<=100000,OFFSET(F29,0,0)>50000),OFFSET(F29,0,0)*0.075,OFFSET(F29,0,0)*0.065)) |
F32:G32 | F32 | =SUM(F30:(OFFSET(F31,0,0))) |
F33:G33 | F33 | =SUM(F25:(OFFSET(F26,0,0))) |
F34:G34 | F34 | =IF(OFFSET(F33,0,0)>1000000,OFFSET(F33,0,0)*0.025,0) |
F35 | F35 | =IF(AND(OFFSET(F4,0,0)>0,OFFSET(DATA!$J2,0,0)="X"),IF(OFFSET(F32,0,0)+OFFSET(F34,0,0)>2000,OFFSET(F32,0,0)+OFFSET(F34,0,0),2000)) |
G36,G31,G26:G28,G23:G24,G20,G9:G11 | G9 | =0 |
G12 | G12 | =SUM(G8:(OFFSET(G8,0,0))) |
G13 | G13 | =0.25 |
G22 | G22 | =IF(OFFSET(Sheet5!E45,0,0)>0,OFFSET(Sheet5!E45,COLUMN()-6,0),IF(OFFSET(Sheet5!E63,0,0)>0,OFFSET(Sheet5!E63,0,0),0)) |
G35 | G35 | =IF(AND(OFFSET(G4,0,0)>0,OFFSET(DATA!$J3,0,0)=X),IF(OFFSET(G32,0,0)>2000,OFFSET(G32,0,0)+OFFSET(G34,0,0),2000)) |
F37:G37 | F37 | =F35-F36 |
VBA Code:
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).FormulaR1C1 = "=SUM(R[-4]C[0]:(OFFSET(R[-4]C[0],0,0)))"
.Cells(13, i).Value = "=.25"
.Cells(14, i).FormulaR1C1 = "=PRODUCT(OFFSET(R[-1]C[0],0,0),R12C3)"
.Cells(15, i).FormulaR1C1 = "=IF(R14C3<=0,0,IF(OFFSET(R[-1]C,0,0)<=OFFSET([Test4.xlsm]Sheet4!R[5]C[-1],0,0),OFFSET(R[-1]C,0,0),OFFSET([Test4.xlsm]Sheet4!R[5]C[-1],0,0)))"
.Cells(16, i).FormulaR1C1 = "=IF(R14C3<0,0,IF(SUM(OFFSET(R[-2]C:R[-1]C,0,0))<0,0,SUM(OFFSET(R[-2]C:R[-1]C,0,0))))"
.Cells(17, i).FormulaR1C1 = "=IF(R16C3<0,0,OFFSET(Sheet4!R[20]C[-1],0,0))"
.Cells(18, i).FormulaR1C1 = "=IF(SUM(OFFSET(R[-2]C:R[-1]C,0,0))<0,0,SUM(OFFSET(R[-2]C:R[-1]C,0,0)))"
.Cells(19, i).FormulaR1C1 = "=-(MIN(R[-1]C,ABS(IF(OFFSET(Sheet5!R[4]C[-2],0,0)>0,OFFSET(Sheet5!R[4]C[-2],0,0),IF(OFFSET(Sheet5!R[63]C[-2],0,0)>0,OFFSET(Sheet5!R[63]C[-2],0,0),0)))))"
.Cells(20, i).Value = "=0"
.Cells(21, i).FormulaR1C1 = "=IF(SUM(OFFSET(R[-3]C:R[-1]C,0,0))<0,0,SUM(OFFSET(R[-3]C:R[-1]C,0,0)))"
.Cells(22, i).FormulaR1C1 = "=IF(OFFSET(Sheet5!R[23]C[-2],0,0)>0,OFFSET(Sheet5!R[23]C[-2],COLUMN()-6,0),IF(OFFSET(Sheet5!R[41]C[-2],0,0)>0,OFFSET(Sheet5!R[41]C[-2],0,0),0))"
.Cells(23, i).Value = "=0"
.Cells(24, i).Value = "=0"
.Cells(25, i).FormulaR1C1 = "=OFFSET(R[-4]C,0,0)+OFFSET(R[-3]C,0,0)-OFFSET(R[-1]C,0,0)"
.Cells(26, i).Value = "=0"
.Cells(27, i).Value = "=0"
.Cells(28, i).Value = "=0"
.Cells(29, i).FormulaR1C1 = "=SUM(R[-4]C:(OFFSET(R[-1]C,0,0)))"
.Cells(30, i).FormulaR1C1 = "=IF(OFFSET(R[-1]C,0,0)>100000,OFFSET(R[-1]C,0,0)*0.09,IF(AND(OFFSET(R[-1]C,0,0)<=100000,OFFSET(R[-1]C,0,0)>50000),OFFSET(R[-1]C,0,0)*0.075,OFFSET(R[-1]C,0,0)*0.065))"
.Cells(31, i).Value = "=0"
.Cells(32, i).FormulaR1C1 = "=SUM(R[-2]C:(OFFSET(R[-1]C,0,0)))"
.Cells(33, i).FormulaR1C1 = "=SUM(R[-8]C:(OFFSET(R[-7]C,0,0)))"
.Cells(34, i).FormulaR1C1 = "=IF(OFFSET(R[-1]C,0,0)>1000000,OFFSET(R[-1]C,0,0)*0.025,0)"
.Cells(35, i).FormulaR1C1 = "=IF(AND(OFFSET(R[-31]C,0,0)>0,OFFSET(DATA!R[-32]C10,0,0)=x),IF(OFFSET(R[-3]C,0,0)>2000,OFFSET(R[-3]C,0,0)+OFFSET(R[-1]C,0,0),2000))"
.Cells(36, i).Value = "=0"
.Cells(37, i).Value = "=R[-2]C-R[-1]C"
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