Unable to set the FormulaArray properties of the Range Class

[g8r777]

I have the following code:

Sub Reconcile()
Dim AcctNo As Integer
Dim CalcDate As Integer
Dim Comments As Integer
Dim rAcctNo As Range
Dim rComments As Range
Dim rXref As Range
Dim LastRow As Double
Dim rng As Range

ActivateWB ("reconcile.xlsx")
Sheets("This Month").Activate

AcctNo = WorksheetFunction.Match("Acct No", Rows("1:1"), 0)

Comments = WorksheetFunction.Match("Comments", Rows("1:1"), 0)

Set rComments = Range((Cells(2, Comments)), (Cells(2, Comments)))

rComments.Name = "Comments"

Set rAcctNo = Range((Cells(2, AcctNo)), (Cells(2, AcctNo)))

Set rng = ActiveSheet.Range("Comments")

LastRow = Cells(Rows.Count, AcctNo).End(xlUp).Row

rng.FormulaArray = "=INDEX(XREF,MATCH(B2&G2,AcctNo&CalcDate,0),10)"

'rng.FormulaArray = "=INDEX(XREF,MATCH(" & rAcctNo &"&G2,AcctNo&CalcDate,0)," & Comments & ")"""

rng.Select
Selection.AutoFill Destination:=Range("K2:K" & LastRow)
Range("K2:K" & LastRow).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

I am trying to replicate an Index formula with a two criteria Match formula imbedded.

The code as shown above works. My problem is the hard coding of B2 and G2 and the hard coding to return column 10.

I have tried using the dynamic coding in the next formula that is in green by referencing rAcctNo and Comments.

When I run the code I get a Run-time error '1004' - Unable to set the FormulaArray properties of the Range Class. However, when the code breaks and the debugger pops up, if I mouse over rng.FormulaArray the debugger shows "=INDEX(XREF,MATCH(B2&G2,AcctNo&CalcDate,0),10)" which is what I want.

I think I am headed in the right direction. Now I just need to get rid of the run-time error.

Any help would be greatly appreciated.

Thank you,

Brian

[Bob Phillips]

Shouldn't it be

rng.FormulaArray = "=INDEX(XREF,MATCH(B2&G2," & AcctNo & "&" & CalcDate & ",0),10)"

[g8r777]

No.

This formula works:

rng.FormulaArray = "=INDEX(XREF,MATCH(B2&G2,AcctNo&CalcDate,0),10)"


I am trying to get rid of the B2 and replace it with rAcctNo and get rid of G2 and replace it with rCalcDate and replace 10 with Comments. I don't want the hard coded B2 and G2 and 10.

While these will work for now, if the input sheets that the data come from have the columns "Acct No", "Calculated Date" and "Comments" move, then my code is broken at that point.

[shrivallabha]

Probably:
Rng.FormulaArray = "=INDEX(XREF,MATCH(" & rAcctNo.Address & rCalcDate.Address & ",AcctNo&CalcDate,0)," & Comments & ")"

[Bob Phillips]

I am trying to get rid of the B2 and replace it with rAcctNo and get rid of G2 and replace it with rCalcDate and replace 10 with Comments. I don't want the hard coded B2 and G2 and 10.

While these will work for now, if the input sheets that the data come from have the columns "Acct No", "Calculated Date" and "Comments" move, then my code is broken at that point.

You should name those cells and use the name in the code.

[g8r777]

If I do that then when the fomula is copied down, instead of the formula changing to B3 and G3 and then B4 and G4 and so on, the formula just copies the same name for the cell. I get 50 lines of the exact same formula.

I cannot get the formula to offset from the named ranges as it is copied down.

I even tried puting the main formula in the first cell and then this formula in the next cell and copying this one down:

=INDEX(Xref,MATCH(AcctNo2+1&CalcDate2+1,AcctNo&CalcDate,0),10)

The problem with this is that this exact formula copies down as well. The +1 doesn't change to +2.

I didn't really expect it to because AcctNo2 and CalcDate2 refer to ranges (even thought they are only 1 cell) and not actual cells.

[shrivallabha]

Does following work:
Rng.FormulaArray = "=INDEX(XREF,MATCH(" & rAcctNo.Address(False,False) & rCalcDate.Address(False, False) & ",AcctNo&CalcDate,0)," & Comments & ")"

[g8r777]

That worked. Thank you.

My only issue now is that the formula returns "0" if both columns don't match. Currently I've been taking care of this by copying and pasting values to get rid of the formula. I then take care of the zeros by writing another formula with and IF statement and then copying and pasting the values.

I guess I can do this with the code but it seems like a lot of work.

[shrivallabha]

That worked. Thank you.

My only issue now is that the formula returns "0" if both columns don't match. Currently I've been taking care of this by copying and pasting values to get rid of the formula. I then take care of the zeros by writing another formula with and IF statement and then copying and pasting the values.

I guess I can do this with the code but it seems like a lot of work.

You can write code for this requirement and it'll not be big code. Something like:
With Rng
.Value = .Value
.Replace 0, vbNullString, xlWhole
End With