Update Array formula using Macro

[payney]

I have created the below formula because I have multiple lookup criteria on an Excel table; however, in order to make it work, I have to individually go into each cell and press Ctrl+Shift+Enter. When I tried to create a macro For Each Cell in Selection every cell in the selection returned the same value, as if it was looking up the same criteria.

=SUM(IF('Data table - actual'!$A:$A=('Actual formula'!B$4&'Actual formula'!$A5),'Data table - actual'!$E:$E))

Does anyone know how to create a macro to individually update each cell within a formula?

Many thanks


Michael

 

[GlennUK]

It sounds like you were setting the Formula property instead of the FormulaArray property. Change your code and try again.

 

[payney]

Glen

Thanks for your reply. I'm a little new to this, so could you explain what you mean in a bit more detail.


Many thanks

Michael

 

[GlennUK]

You said:

...When I tried to create a macro For Each Cell in Selection every cell in the selection returned the same value, as if it was looking up the same criteria....

Well, in that macro you must have used the Formula property to set up the formulas. You should have used the FormulaArray property.

 

[payney]

Probably would have helped if i showed you the code!

For Each cell In Selection
Selection.FormulaArray = _
"=SUM(IF('Data table - actual'!C1=('Actual formula'!R4C&'Actual formula'!RC1),'Data table - actual'!C5))"
Next


So, when I press "go" on the macro, all the formula becomes the "SUM(IF)" formula stated above. How do ctrl+shift+enter the formula currently in the cell without replacing it with the formula above?

 

[GlennUK]

You have an error in your code:

For Each cell In Selection
cell.FormulaArray = _
"=SUM(IF('Data table - actual'!C1=('Actual formula'!R4C&'Actual formula'!RC1),'Data table - actual'!C5))"
Next

 

[payney]

Glen, that it absolutely fantastic!

Thank you eversomuch for your help. Unfortunately, I have quite a few cells to update, which takes quite a while using this code. Don't suppose you have anymore great ideas on how to quicken it up?


Many thanks


Michael