How to use R1C1 formatted text in a formula

Question

In order to get dynamic formulas to develop through VBA, I had to use INDIRECT(ADDRESS(. I am now stuck with several dozen formulas that look like this:

=INDIRECT(ADDRESS(37,10),1)*(INDIRECT(ADDRESS(37,7),1)+(INDIRECT(ADDRESS(69,10),1)*INDIRECT(ADDRESS(77,10),1))+(INDIRECT(ADDRESS(70,10),1)*INDIRECT(ADDRESS(78,10),1))+(INDIRECT(ADDRESS(71,10),1)*INDIRECT(ADDRESS(79,10),1))+(INDIRECT(ADDRESS(72,10),1)*INDIRECT(ADDRESS(80,10),1))+(INDIRECT(ADDRESS(73,10),1)*INDIRECT(ADDRESS(81,10),1))+(INDIRECT(ADDRESS(74,10),1)*INDIRECT(ADDRESS(82,10),1))+(INDIRECT(ADDRESS(75,10),1)*INDIRECT(ADDRESS(83,10),1)))

I cannot use these formulas with Solver because it is too much for Solver. When I manually translate the formulas to "A1" format, then Solver works. I need to automate the translation because this is meant to be a template.

I was hoping that replacing "INDIRECT(ADDRESS" with "Cells" might work. No luck. Is there an expression I can use that can replace "INDIRECT(ADDRESS" that will read the R1C1 format? Or do you have a better solution?

Thanks!

 r = 0
    Do Until r = 6
        i = 0
        Do Until i = DCShipFrom
        Cells(70 + r, 7 + DCShipFrom + i).Select
        Selection.FormulaR1C1 = "=IF(AND(INDIRECT(ADDRESS(" & 67 & "," & 7 + DCShipFrom + i & "),1)<=" & "INDIRECT(ADDRESS(" & 70 + r & "," & 6 & "),1)," & "INDIRECT(ADDRESS(" & 67 & "," & 7 + DCShipFrom + i & "),1)>" & "INDIRECT(ADDRESS(" & 69 + r & "," & 6 & "),1)),1,0)"
        i = i + 1
        Loop
    r = r + 1
    Loop
    enter code here

I don't see how this is dynamic as `INDIRECT(ADDRESS(37,10),1)*...` = `$J$31*...` — Scott Craner, Nov 10 '17 at 17:59
I had to use INDIRECT(ADDRESS in my VBA to create the dynamic equations. The long formula is the result. I need the long formula to be simplified so Solver can work. — ShNBl84, Nov 10 '17 at 18:02
That is the point I do not see how this is dynamic. `INDIRECT(ADDRESS(37,10),1)` will always refer to $J$31 that is not dynamic. So we are missing some information. please include the actual code. — Scott Craner, Nov 10 '17 at 18:03
I think I am miscommunicating. I no longer need anything to be dynamic because the VBA did it for me. Now I am stuck with formulas like above. Do you know of a way to simplify only the formula so Solver can use it? — ShNBl84, Nov 10 '17 at 18:08
The code has been added, but that is not my question. The formulas are done and they work, I just need the finished formulas simplified. — ShNBl84, Nov 10 '17 at 18:12
Not in the comments, please. Put the code in the original post using [edit] — Scott Craner, Nov 10 '17 at 18:13
Yes the problem is with how you assume r1c1 formulas work. You do not need the INDIRECT(ADDRESS()) to make it dynamic. You just need to create a simple formula dynamically. — Scott Craner, Nov 10 '17 at 18:23
*I had to use INDIRECT(ADDRESS in my VBA to create the dynamic equations.* So what does =INDIRECT(ADDRESS(37,10) give you that =INDIRECT($J$37 doesn't? Also note that INDIRECT is often a poor choice compared to functions such as CHOOSE, INDEX (reference version) or other approaches. Especilaly when used in VBA, as it only takes someone to add or remove rows or columns and suddenly your formulas are pointing to the wrong place. Suggest you lodge a new question that addresses the cause of your issue (bad formula choice) rather than the symptoms. — jeffreyweir, Nov 10 '17 at 18:56

score 0 · Answer 1 · answered Nov 10 '17 at 18:06

With ADDRESS(37, 10) returning $J$37 use,

INDIRECT(ADDRESS(37, 10), 1)  'or INDIRECT(ADDRESS(37,10)) since xlA1 is default

With ADDRESS(37, 10, 1, 0) returning R37C10 use,

INDIRECT(ADDRESS(37, 10, 1, 0), 0)  'the zeroes denote xlR1C1 addressing

You might want to consider one of the following,

INDEX($J:$J, 37)
INDEX($A:$Z, 37, 10)

INDEX is non-volatile. Both INDIRECT and ADDRESS are volatile.

score 0 · Answer 2 · answered Nov 10 '17 at 18:08

0

You can use INDIRECT with R1C1 format by adding ,FALSE at the end of your indirect statement and to make it dynamic you make the row and column variables e.g.

INDIRECT("R" & myrow & "C" & mycol,FALSE)

answered Nov 10 '17 at 18:08

Carol

471
4
7

Scott Craner · Accepted Answer · 2017-11-10T18:50:48.463

0

Try this, it makes the formulas dynamic, but much simpler:

For r = 0 To 6
    For i = 0 To DCShipFrom
        With ActiveSheet
            .Cells(70 + r, 7 + DCShipFrom + i).Formula = "=IF(AND(" & .Cells(67, 7 + DCShipFrom + i).Address(0, 0) & "<=" & .Cells(70 + r, 6).Address(0, 0) & "," & .Cells(67, 7 + DCShipFrom + i).Address & " >" & .Cells(69 + r, 6).Address & "),1,0)"
        End With
    Next i
next r

Or if you want R1C1:

For r = 0 To 6
    For i = 0 To DCShipFrom
        With ActiveSheet
            .Cells(70 + r, 7 + DCShipFrom + i).FormulaR1C1 = "=IF(AND(R" & 67 & "C" & 7 + DCShipFrom + i & " <= R" & 70 + r & "C" & 6 & ",R" & 67 & "C" & 7 + DCShipFrom + i & " > R" & 69 + r & "C" & 6 & "),1,0)"
        End With
    Next i
Next r

edited Nov 10 '17 at 18:50

answered Nov 10 '17 at 18:21

Scott Craner

148,073
10
49
81

Thanks for the help @Scott! It took me a while to reply because I was getting an error. I added a closing ) right before the ,1 at the end. This should solve my problem. I really appreciate it! – ShNBl84 Nov 10 '17 at 18:49
@ShNBl84 yup missed that. sorry. It is in there now. Glad to help. Sometime it is better to get to the root than to try and change the end result. – Scott Craner Nov 10 '17 at 18:51

How to use R1C1 formatted text in a formula

3 Answers3