Help with R1C1 Formula

Tdorman · Aug 17, 2021

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

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

3

4

5

6

7

8

-7,181,711

-76,340

9

0

10

0

11

0

12

-7,181,711

-76,340

13

1.000000

14

15

16

17

18

19

20

0

21

0

22

0

23

0

24

0

25

0

26

0

27

0

28

0

29

0

30

0

31

0

32

33

34

35

36

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

jorismoerings · Aug 17, 2021

Tdorman said:
The first issue is that having an X in the code is not working

Change your code to :

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))"

Tdorman said:
The current code does not increase the row, presumably because it is not pulling the formula down rows, rather across columns.

I'm unsure what you're trying to achieve with this comment, can you eloborate on this part.

Tdorman · Aug 18, 2021

The code worked for adding the X correctly, thank you. However, the issue with the second part is still there. What is happening is that member 1 is showing DATA!$J2 for this part of the formula DATA!R[-32]C10,0,0. Every other column that is inserted is showing DATA!$J3. I need the row number to increase by one for each new column that is inserted, not stop at J3, i.e. J3, J4, J5, J6, and so on.

jorismoerings · Aug 18, 2021

Tdorman said:
The code worked for adding the X correctly, thank you. However, the issue with the second part is still there. What is happening is that member 1 is showing DATA!$J2 for this part of the formula DATA!R[-32]C10,0,0. Every other column that is inserted is showing DATA!$J3. I need the row number to increase by one for each new column that is inserted, not stop at J3, i.e. J3, J4, J5, J6, and so on.

Hi,

Could you drop your testfile in a public fileshare (Dropbox or Box) so i can pick it up and check with your data to recreate your issue?

Tdorman · Aug 18, 2021

Working on getting an edited version I can upload.

Here is what Im working on in the meantime

Test4EDIT.xlsm

F

G

H

I

35

2,000

36

0

37

2,000

C-Proposal

Cell Formulas
Range	Formula
F35	F35	=IF(AND(OFFSET(F4,0,0)>0,OFFSET(DATA!$J2,0,0)="x"),IF(OFFSET(F32,0,0)>2000,OFFSET(F32,0,0)+OFFSET(F34,0,0),2000))
G35:I35	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))
F36:I36	F36	=0
F37:I37	F37	=F35-F36

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:XFD37	Expression	=NOT(EXACT(B1, ""))	text	NO

As you can see, the formula is dragging over J3 and not increasing to J4, J5, and so on. This is the data sheet it's referencing

Test4EDIT.xlsm

A

B

C

D

E

F

G

H

I

J

K

1

UnitaryID

ManagerialEIN(*LineOneOnly*)

MemberName

MemberEIN

NJCorpNumber

StateCountry

BusActCd

TypeOfBus

PrincipalProducts

Nexus

Inactive

2

N/A

111111111

AAAAAAAAA

111111111

N/A

X

3

N/A

222222222

BBBBBBBBB

222222222

N/A

X

4

N/A

333333333

CCCCCCCCC

333333333

N/A

X

5

N/A

444444444

DDDDDDDDD

444444444

N/A

0

6

N/A

555555555

EEEEEEEEE

555555555

N/A

0

7

N/A

666666666

FFFFFFFFF

666666666

N/A

0

8

N/A

777777777

GGGGGGGGG

777777777

N/A

0

9

N/A

888888888

HHHHHHHHH

888888888

N/A

0

10

N/A

999999999

IIIIIIIII

999999999

N/A

0

11

N/A

1111111110

JJJJJJJJJ

1111111110

N/A

0

12

N/A

1222222221

KKKKKKKKK

1222222221

N/A

0

13

N/A

1333333332

LLLLLLLLL

1333333332

N/A

0

14

N/A

1444444443

MMMMMMMMM

1444444443

N/A

0

15

N/A

1555555554

NNNNNNNNN

1555555554

N/A

0

16

N/A

1666666665

OOOOOOOOO

1666666665

N/A

0

17

N/A

1777777776

PPPPPPPPP

1777777776

N/A

0

18

N/A

1888888887

QQQQQQQQQ

1888888887

N/A

0

19

N/A

1999999998

RRRRRRRRR

1999999998

N/A

0

20

N/A

2111111109

SSSSSSSSS

2111111109

N/A

0

21

N/A

2222222220

TTTTTTTTT

2222222220

N/A

0

22

N/A

2333333331

UUUUUUUUU

2333333331

N/A

0

23

N/A

2444444442

VVVVVVVVV

2444444442

N/A

0

24

N/A

2555555553

WWWWWWWWW

2555555553

N/A

0

25

N/A

2666666664

XXXXXXXXX

2666666664

N/A

0

26

N/A

2777777775

YYYYYYYYY

2777777775

N/A

0

27

N/A

2888888886

ZZZZZZZZZ

2888888886

N/A

0

28

N/A

2999999997

ABCDEFGHI

2999999997

N/A

0

DATA

I need the formula to stay in column J but increase the row.

jorismoerings · Aug 18, 2021

Tdorman said:
The code worked for adding the X correctly, thank you. However, the issue with the second part is still there. What is happening is that member 1 is showing DATA!$J2 for this part of the formula DATA!R[-32]C10,0,0. Every other column that is inserted is showing DATA!$J3. I need the row number to increase by one for each new column that is inserted, not stop at J3, i.e. J3, J4, J5, J6, and so on.

But if you translate the formula you've posted it relates directly to Data$J3. If you insert column that would never lead to a row increase like you've asked for. So still not sure what you want to achieve.
If you want DATA!$J3 to change to DATA!$K3 when insert a new column, change your formula to

VBA Code:

.Cells(35, i).FormulaR1C1 = "=IF(AND(OFFSET(R[-31]C;0;0)>0;OFFSET(data!R[-32]C[4];0;0)=""x"");IF(OFFSET(R[-3]C;0;0)>2000;OFFSET(R[-3]C;0;0)+OFFSET(R[-1]C;0;0);2000))"

Tdorman · Aug 18, 2021

I need the formula to stay in column J but increase the row. Would a lookup function work? After reviewing it further, the and function can come out of the formula with the way the sheet is working currently. If use a vlookup to see if there is an X in column J based on the member name, and then have the rest of the formula in there, would that work? I've tried the following, but it gives me a #NAME? error.

Excel Formula:

=IF(VLOOKUP(F3,DATA!$C$2:$J$20000,J,FALSE)="X",IF('C-Proposal'!F34+'C-Proposal'!F32>2000,'C-Proposal'!F32+'C-Proposal'!F34,2000))

If there is an X in the column for that member then the minimum in row 35 will be 2000. If row 32 and 34 are greater than 2000 it will be the sum of those 2 cells.

Tdorman · Aug 18, 2021

So the formula would be this

Excel Formula:

=IF(VLOOKUP(F3,DATA!$C$2:$J$20000,8,FALSE)="X",IF('C-Proposal'!F34+'C-Proposal'!F32>2000,'C-Proposal'!F32+'C-Proposal'!F34,2000))

Would something like this in R1C1 format work instead? My macro to convert this to R1C1 format isn't working for this formula

Tdorman · Aug 18, 2021

That appears to have worked, thanks again for your help

jorismoerings · Aug 18, 2021

Hi,

just as a tip. If you need to convert a formula to R1C1 format: just type it like you used to. Confirm with enter. Go to file menu, choose options, select formula.
in the windows there’s an option to change formula to R1C1 style, check the box. Go back to your sheet and excel has changed your formula to R1C1. Copy it to your VBA and turn R1C1 stylemoff by reversing the previous mentioned steps.

Help with R1C1 Formula

Tdorman

Board Regular

Excel Facts

jorismoerings

Well-known Member

Tdorman

Board Regular

jorismoerings

Well-known Member

Tdorman

Board Regular

jorismoerings

Well-known Member

Tdorman

Board Regular

Tdorman

Board Regular

Tdorman

Board Regular

jorismoerings

Well-known Member

Similar threads

Forum statistics

Share this page

Help with R1C1 Formula

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock