How can I use an array formula in a macro

I'm trying to have an array formula in a macro. But its return an error. Cansomeone help me please.
Attached is my workings with a recorded macro. The formula in column "O" returns the results I require. My request is for a macro to the same result in column "N" or any "active cell" selected.
Your assistence would be appreciated.

Thanks
IndexMatchTest.xlsm
user2073Asked:
Who is Participating?
 
krishnakrkcConnect With a Mentor Commented:
Hi

like this

Sub MacroTest()
    
    Dim rngFormula      As Range
    Dim rngSource       As Range
    Dim TypeCol         As Long
    Dim strTypeAddr     As String
    Dim strConcatAddr   As String
    Dim MyDate          As Date
    
    Const Item_Col      As Long = 1
    Const Concat_Col    As Long = 11
    
    MyDate = DateSerial(2011, 5, 1)     '<<< adjust the date
    
    Set rngFormula = Worksheets("Sheet1").Range("o6:o14")
    Set rngSource = Worksheets("Sheet2").Range("a5").CurrentRegion
    
    TypeCol = WorksheetFunction.Match("Type", rngSource.Rows(1), 0)
    
    strTypeAddr = rngSource.Columns(TypeCol).Address(, , -4150, external:=1)        'Type range address
    strConcatAddr = rngSource.Columns(Concat_Col).Address(, , -4150, external:=1)   'Concat range address
    
    With rngFormula
        .FormulaR1C1 = "=iferror(index(" & strTypeAddr & ",match(" & CLng(MyDate) & "&rc" & Item_Col & "," & strConcatAddr & ",0)),"""")"
    End With
    
End Sub

Open in new window


Kris
0
 
krishnakrkcCommented:
Hi,

Using a helper column

In K5 and copied down on Sheet2

=DATEVALUE(G5&F5)&B5

and try this code.

Sub MacroTest()
    
    Dim rngFormula      As Range
    Dim rngSource       As Range
    Dim TypeCol         As Long
    Dim strTypeAddr     As String
    Dim strConcatAddr   As String
    
    Const Item_Col      As Long = 1
    Const Concat_Col    As Long = 11
    
    Set rngFormula = Worksheets("Sheet1").Range("o6:o14")
    Set rngSource = Worksheets("Sheet2").Range("a5").CurrentRegion
    
    TypeCol = WorksheetFunction.Match("Type", rngSource.Rows(1), 0)
    
    strTypeAddr = rngSource.Columns(TypeCol).Address(, , -4150, external:=1)        'Type range address
    strConcatAddr = rngSource.Columns(Concat_Col).Address(, , -4150, external:=1)   'Concat range address
    
    'r2c3-- > Date range address
    
    With rngFormula
        .FormulaR1C1 = "=iferror(index(" & strTypeAddr & ",match(r2c3&rc" & Item_Col & "," & strConcatAddr & ",0)),"""")"
    End With
    
End Sub

Open in new window


Kris
0
 
user2073Author Commented:
The above code works well. Is it possible to place the "Datevalue" formula into memory instead of writing it into a helper column.

Thanks
0
 
user2073Author Commented:
I'll work with the Code later today. Thanks for the solutions. I appreciate your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.