CODEWITHSUNDEEP
vbaexcelexcel-formula
Aapich.S
Aapich.S

Reputation: 11

Excel Formulas to excel-vba

I have this formula that looks at various criteria across multiple columns and checks to see that if all the all the criteria match, it will paste data from one column to another. I've tried a couple ways to get it into VBA, but I can't seem to get anything to work. Thanks!

=IFERROR(INDEX(Sheet1!A$2:A$205,SMALL(IF(ISNUMBER(SEARCH("ECR Approval",Sheet1!$C$2:$C$205)),ROW(Sheet1!$A$2:$A$205)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A2))),"")

Upvotes: 1

Views: 96

Answers (2)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

This is an Array Formula and you can place this formula on the Sheet by using the following code...

Dim ws As Worksheet
Set ws = Sheets("Sheet2")   'Sheet where formula would be placed
ws.Range("A2").FormulaArray = "=IFERROR(INDEX(Sheet1!A$2:A$205,SMALL(IF(ISNUMBER(SEARCH(""ECR Approval"",Sheet1!$C$2:$C$205)),ROW(Sheet1!$A$2:$A$205)-ROW(Sheet1!$A$2)+1),ROWS(A$2:A2))),"""")"
ws.Range("A2").AutoFill ws.Range("A2:A205"), xlFillDefault

Upvotes: 3

Moosli
Moosli

Reputation: 3285

To use a function in VBA, you need to use before each function Application.WorksheetFunction.

x = Application.WorksheetFunction.Sum(y,z)

To reference a cell in a sheet in VBA you can use Rage

x = Application.WorksheetFunction.Sum(Range("A1:A2"))

Put this to things Together and it would look like this:

=Application.WorksheetFunction.IFERROR(Application.WorksheetFunction.Index(Worksheet(1).Range("A2:A205"),Application.WorksheetFunction.SMALL(Application.WorksheetFunction.IF(Application.WorksheetFunction.ISNUMBER(....

Upvotes: 1

Related Questions