FormulaR1C1 VBA Question

fenerian

New Member
Joined
Mar 13, 2009
Messages
16
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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:

Code:
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,
 
Upvote 0
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:

Code:
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:

Code:
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.
 
Upvote 0
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:

Code:
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,
 
Upvote 0

Forum statistics

Threads
1,224,524
Messages
6,179,304
Members
452,904
Latest member
CodeMasterX

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
Back
Top