PDA

View Full Version : Solved: Using Formulas matrix in VBA and drag down



marreco
02-05-2013, 05:46 PM
Hi
I'm trying to use 2 matrix formulas with VBA

but poralgum reason is going wrong.

formulas search data on a giant table, being many data does not support my PC and it takes a lot to process.
Sub FillFormula()
Dim lr As Long
Const sFormula1 As String = "=IF(A2="""";"""";IF(ISERROR(LARGE(IF($A$2:$A$10000=A2;ROW($A$2:$A$10000));ROW(INDIRECT(""1:""&ROWS($A$2:$A$10000))))-1);"""";INDEX($B$2:$B$10000;LARGE(IF($A$2:$A$10000=A2;ROW($A$2:$A$10000));ROW(INDI RECT(""1:""&ROWS($A$10000))))-1)))"
Const sFormula2 As String = "=IF(A2="""";"""";IF(ISERROR(LARGE(IF($A$2:$A$10000=A2;ROW($A$2:$A$10000));ROW(INDIRECT(""1:""&ROWS($A$2:$A$10000))))-1);"""";INDEX($C$2:$C$10000;LARGE(IF($A$2:$A$10000=A2;ROW($A$2:$A$10000));ROW(INDI RECT(""1:""&ROWS($A$10000))))-1)))"

With Sheets("Plan1")
lr = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
.Range("D2").Resize(lr).Formula = sFormula1
.Range("E2").Resize(lr).Formula = sFormula2
.Range("D2").Resize(lr, 2).Value = .Range("D2").Resize(lr, 2).Value
End With
End Sub


Thank you!!

Bob Phillips
02-06-2013, 05:17 AM
What's the question? And what is poralgum supposed to be?

marreco
02-06-2013, 05:28 AM
Hi.
I'm trying to use 2 matrix formulas with VBA
but for some reason, is going wrong
formulas search data on a giant table, being many data does not support my PC and it takes a lot to process.

Bob Phillips
02-06-2013, 05:44 AM
When it is clear I am not understanding what you wrote, repeating exactly the same words doesn't seem the best approach.

So, does it jsut take a long time, or does it crash, or does it give wrong results.

What is that formula supposed to be doing?

What does ... many data does not support my PC ... mean?

marreco
02-06-2013, 05:49 AM
I'm trying to adapt the two formulas (matrix) in a VBA code.

When running generates an error (seems to be the formula)

Run-time error '1004 ':
Error application definition or object definition
This line is yellow.
. Range ("D2"). Resize (lr). Formula = sFormula1

Bob Phillips
02-06-2013, 06:22 AM
If you won't answer my questions, there is little I can do to help you.

marreco
02-06-2013, 06:27 AM
Hi.


What is That formula supposed to be doing? returns the largest data in column "B" according to criterion in column "A"


What does ... many data does not support my PC ... mean? my computer émuito weak by many the data in excel, my computer can not handle

marreco
02-06-2013, 01:18 PM
Hi
Cross-Post
http://www.excelforum.com/excel-programming-vba-macros/897924-using-formulas-matrix-in-vba-and-drag-down.html?p=3114996#post3114996

marreco
02-06-2013, 03:01 PM
Hi.
solved by p24leclerc
the error was in "," comma

Thank you!!!

Bob Phillips
02-06-2013, 03:41 PM
It may not be erroring now, but is it actually working. It seems to me that should be an array formula but you are not array-entering it, so i will return wrong results.

marreco
02-06-2013, 04:11 PM
Hi.

because I can not make it work Array Formula in VBA?
I tried another way but not solved
Sub FillFormula()
Dim lr As Long
Application.ScreenUpdating = False
Const sFormula1 As String = "=IF(A2="""","""",MAX(IF($A$2:$A$5000=A2,$B$2:$B$5000)))"
Const sFormula2 As String = "=IF(A2="""","""",INDEX($C$2:$C$5000,MATCH(A2&D2,$A$2:$A$5000&$B$2:$B$5000,0)))"

With Sheets("Plan1")
lr = .Cells(.Rows.Count, "A").End(xlUp).ROW + 1
.Range("D2").Resize(lr).FormulaArray = sFormula1
.Range("E2").Resize(lr).FormulaArray = sFormula2
.Range("D2").Resize(lr, 2).Value = .Range("D2").Resize(lr, 2).Value
End With
Application.ScreenUpdating = True
End Sub

Bob Phillips
02-06-2013, 04:49 PM
Sub FillFormula()
Dim lr As Long
Application.ScreenUpdating = False
Const sFormula1 As String = "=IF(A2="""","""",MAX(IF($A$2:$A$5000=A2,$B$2:$B$5000)))"
Const sFormula2 As String = "=IF(A2="""","""",INDEX($C$2:$C$5000,MATCH(A2&D2,$A$2:$A$5000&$B$2:$B$5000,0)))"

With Sheets("Plan1")
lr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Range("D2").FormulaArray = sFormula1
.Range("E2").FormulaArray = sFormula2
.Range("E2:D2").AutoFill .Range("E2:D2").Resize(lr - 1)
.Range("D2").Resize(lr, 2).Value = .Range("D2").Resize(lr, 2).Value
End With
Application.ScreenUpdating = True
End Sub

marreco
02-06-2013, 05:29 PM
Hi.

Perfect!!!

Thank you very much!!