g8r777
08-26-2012, 07:12 PM
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
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