Help with R1C1 Formula

Tdorman

New Member
Joined
Aug 12, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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
FG
1Managerial MemberMember2
2NU1008976543NU1008976543
3AAAAAAAAABBBBBBBBB
4111111111222222222
5
6
7
8-7,181,711-76,340
900
1000
1100
12-7,181,711-76,340
131.0000000.250000
14-7,258,051-1,814,513
1500
1600
1700
1800
1900
2000
2100
2200
2300
2400
2500
2600
2700
2800
2900
3000
3100
3200
3300
3400
352,000#NAME?
3600
372,000#NAME?
Sheet3
Cell Formulas
RangeFormula
F2:G2F2=DATA!$A$2
F3:G3F3=OFFSET(DATA!$C$2,COLUMN()-6,0)
F4:G4F4=OFFSET(DATA!$D$2,COLUMN()-6,0)
F8:G8F8=OFFSET(DATA!$L$2,COLUMN()-6,0)
F12,F29:G29F12=SUM(F8:(OFFSET(F11,0,0)))
F14:G14F14=PRODUCT(OFFSET(F13,0,0),$C$12)
F15:G15F15=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:G16F16=IF($C$14<0,0,IF(SUM(OFFSET(F14:F15,0,0))<0,0,SUM(OFFSET(F14:F15,0,0))))
F17:G17F17=IF($C$16<0,0,OFFSET(Sheet4!E37,0,0))
F18:G18F18=IF(SUM(OFFSET(F16:F17,0,0))<0,0,SUM(OFFSET(F16:F17,0,0)))
F19:G19F19=-(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:G21F21=IF(SUM(OFFSET(F18:F20,0,0))<0,0,SUM(OFFSET(F18:F20,0,0)))
F22F22=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))
F24F24=IF(SUM(OFFSET(F21:F22,0,0))<=0,0,PRODUCT(OFFSET(F13,0,0)*OFFSET(F23,0,0)))
F25:G25F25=OFFSET(F21,0,0)+OFFSET(F22,0,0)-OFFSET(F24,0,0)
F30:G30F30=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:G32F32=SUM(F30:(OFFSET(F31,0,0)))
F33:G33F33=SUM(F25:(OFFSET(F26,0,0)))
F34:G34F34=IF(OFFSET(F33,0,0)>1000000,OFFSET(F33,0,0)*0.025,0)
F35F35=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:G11G9=0
G12G12=SUM(G8:(OFFSET(G8,0,0)))
G13G13=0.25
G22G22=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))
G35G35=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:G37F37=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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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))"

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

New Member
Joined
Aug 12, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 4, 2014
Messages
1,482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Aug 12, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Working on getting an edited version I can upload.

Here is what Im working on in the meantime

Test4EDIT.xlsm
FGHI
352,0002,0002,0002,000
360000
372,0002,0002,0002,000
C-Proposal
Cell Formulas
RangeFormula
F35F35=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:I35G35=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:I36F36=0
F37:I37F37=F35-F36
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1:XFD37Expression=NOT(EXACT(B1, ""))textNO


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
ABCDEFGHIJK
1UnitaryIDManagerialEIN(*LineOneOnly*)MemberNameMemberEINNJCorpNumberStateCountryBusActCdTypeOfBusPrincipalProductsNexusInactive
2N/A111111111AAAAAAAAA111111111N/AN/AN/AN/AXX
3N/A222222222BBBBBBBBB222222222N/AN/AN/AN/AXX
4N/A333333333CCCCCCCCC333333333N/AN/AN/AN/AN/AX
5N/A444444444DDDDDDDDD444444444N/AN/AN/AN/AN/A00
6N/A555555555EEEEEEEEE555555555N/AN/AN/AN/AN/A00
7N/A666666666FFFFFFFFF666666666N/AN/AN/AN/AN/A00
8N/A777777777GGGGGGGGG777777777N/AN/AN/AN/AN/A00
9N/A888888888HHHHHHHHH888888888N/AN/AN/AN/AN/A00
10N/A999999999IIIIIIIII999999999N/AN/AN/AN/AN/A00
11N/A1111111110JJJJJJJJJ1111111110N/AN/AN/A00
12N/A1222222221KKKKKKKKK1222222221N/AN/AN/A00
13N/A1333333332LLLLLLLLL1333333332N/AN/AN/AN/A00
14N/A1444444443MMMMMMMMM1444444443N/AN/AN/AN/AN/A00
15N/A1555555554NNNNNNNNN1555555554N/AN/AN/AN/A00
16N/A1666666665OOOOOOOOO1666666665N/AN/AN/AN/AN/A00
17N/A1777777776PPPPPPPPP1777777776N/AN/AN/AN/AN/A00
18N/A1888888887QQQQQQQQQ1888888887N/AN/AN/AN/AN/A00
19N/A1999999998RRRRRRRRR1999999998N/AN/AN/AN/A00
20N/A2111111109SSSSSSSSS2111111109N/AN/AN/AN/A00
21N/A2222222220TTTTTTTTT2222222220N/AN/AN/AN/AN/A00
22N/A2333333331UUUUUUUUU2333333331N/AN/AN/AN/AN/A00
23N/A2444444442VVVVVVVVV2444444442N/AN/AN/AN/AN/A00
24N/A2555555553WWWWWWWWW2555555553N/AN/AN/AN/AN/A00
25N/A2666666664XXXXXXXXX2666666664N/AN/AN/AN/A00
26N/A2777777775YYYYYYYYY2777777775N/AN/AN/AN/A00
27N/A2888888886ZZZZZZZZZ2888888886N/AN/AN/A00
28N/A2999999997ABCDEFGHI2999999997N/AN/AN/AN/A00
DATA



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

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Aug 12, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Aug 12, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 12, 2021
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
That appears to have worked, thanks again for your help
 

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,241
Messages
5,745,583
Members
423,963
Latest member
lwilson3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top