Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I use an array formula in a macro

Posted on 2012-08-27
4
Medium Priority
?
351 Views
Last Modified: 2012-08-27
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
0
Comment
Question by:user2073
  • 2
  • 2
4 Comments
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38339386
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
 

Author Comment

by:user2073
ID: 38339507
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
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 38339661
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
 

Author Closing Comment

by:user2073
ID: 38339681
I'll work with the Code later today. Thanks for the solutions. I appreciate your help.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

804 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question