FormulaR1C1 VBA Question

[fenerian]

Hi all,

I am trying to insert a formula to a cell with the line below.

ActiveCell.FormulaR1C1 = "=IF(OR($E$6=Balance!$H$26,$E$6=Balance!$H$27,$E$6=Balance!$H$28),INDEX(INDIRECT("'XA"&$E$6&"'!$E$15:$GH$200"),MATCH(E$5,INDIRECT("'XA"&$E$6&"'!$D$15:$D$200"),0),MATCH($C9,INDIRECT("'XA"&$E$6&"'!$E$14:$GH$14"),0)),INDEX(INDIRECT("'XA"&$E$6&"'!$E$15:$GH$200"),MATCH(E$5,INDIRECT("'XA"&$E$6&"'!$D$15:$D$200"),0),MATCH($C9,INDIRECT("'XA"&$E$6&"'!$E$14:$GH$14"),0))+INDEX(INDIRECT("'XA"&$E$7&"'!$E$15:$GH$200"),MATCH(E$5,INDIRECT("'XA"&$E$7&"'!$D$15:$D$200"),0),MATCH($C9,INDIRECT("'XA"&$E$7&"'!$E$14:$GH$14"),0)))"

The issue I am having is that VBA code comments out the part of the formula that comes after ', see below.

(INDIRECT(" 'XA

Thanks in advance.

 

[Smitty]

You need to use double qoutes.

The easiest way to get complicated formulas converted to code is to record a macro, and hit F2-->Enter on the cell with the formula:

ActiveCell.FormulaR1C1 = _
        "=IF(OR(R6C5=Balance!R26C8,R6C5=Balance!R27C8,R6C5=Balance!R28C8),INDEX(INDIRECT(""'XA""&R6C5&""'!$E$15:$GH$200""),MATCH(R5C[4],INDIRECT(""'XA""&R6C5&""'!$D$15:$D$200""),0),MATCH(R[8]C3,INDIRECT(""'XA""&R6C5&""'!$E$14:$GH$14""),0)),INDEX(INDIRECT(""'XA""&R6C5&""'!$E$15:$GH$200""),MATCH(R5C[4],INDIRECT(""'XA""&R6C5&""'!$D$15:$D$200""),0),MATCH(R[8]C3,INDIRECT(""'XA""&" & _
        "!$E$14:$GH$14""),0))+INDEX(INDIRECT(""'XA""&R7C5&""'!$E$15:$GH$200""),MATCH(R5C[4],INDIRECT(""'XA""&R7C5&""'!$D$15:$D$200""),0),MATCH(R[8]C3,INDIRECT(""'XA""&R7C5&""'!$E$14:$GH$14""),0)))"

HTH,

 

[shg]

You can’t enter an array formula longer than 255 characters via VBA in a single go. At http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/, **** Kusleika shows how a formula might be divided it into parts and entered piecemeal, entering the first part, and then the balance via substitution. Here's the example:

Public Sub LongArrayFormula()
    Dim theFormulaPart1 As String
    Dim theFormulaPart2 As String
    theFormulaPart1 = "=IF(MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1))-" & _
                          "MONTH(DATE(YEAR(NOW()),MONTH(NOW()),1)-" & _
                          "(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+" & _
                          "{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),""""," & _
                          "X_X_X())"
                          
    theFormulaPart2 = "DATE(YEAR(NOW()),MONTH(NOW()),1)-" & _
                      "(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW()),1))-1)+" & _
                      "{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)"
        
    With ActiveSheet.Range("E2:K7")
        .FormulaArray = theFormulaPart1
        .Replace "X_X_X())", theFormulaPart2
        .NumberFormat = "mmm dd"
    End With
End Sub

The tricky part is that the formula must be syntactically correct both initially and following each substitution. Here’s an alternative using SendKeys:

Sub InsertLongArrayFormula(r As Range, _
                                  sFrm As String, _
                                  Optional sFmt As String = "General")
    With r
        .NumberFormat = "@"
        .Value = sFrm
        .NumberFormat = sFmt
        Application.Goto .Cells
    End With
 
    DoEvents
    Application.SendKeys "{F2}^+~"
End Sub

SendKeys is widely regarded as unreliable—I’ve never had a problem with it, but use it so infrequently that that doesn't say much. There is probably an API way to send a message to the window, but I don't know how.

 

[fenerian]

Smitty, thanks alot. Thank you for the tip.

You need to use double qoutes.

The easiest way to get complicated formulas converted to code is to record a macro, and hit F2-->Enter on the cell with the formula:

ActiveCell.FormulaR1C1 = _
        "=IF(OR(R6C5=Balance!R26C8,R6C5=Balance!R27C8,R6C5=Balance!R28C8),INDEX(INDIRECT(""'XA""&R6C5&""'!$E$15:$GH$200""),MATCH(R5C[4],INDIRECT(""'XA""&R6C5&""'!$D$15:$D$200""),0),MATCH(R[8]C3,INDIRECT(""'XA""&R6C5&""'!$E$14:$GH$14""),0)),INDEX(INDIRECT(""'XA""&R6C5&""'!$E$15:$GH$200""),MATCH(R5C[4],INDIRECT(""'XA""&R6C5&""'!$D$15:$D$200""),0),MATCH(R[8]C3,INDIRECT(""'XA""&" & _
        "!$E$14:$GH$14""),0))+INDEX(INDIRECT(""'XA""&R7C5&""'!$E$15:$GH$200""),MATCH(R5C[4],INDIRECT(""'XA""&R7C5&""'!$D$15:$D$200""),0),MATCH(R[8]C3,INDIRECT(""'XA""&R7C5&""'!$E$14:$GH$14""),0)))"

HTH,