Data function

On 04/27/2018 at 17:09

Good afternoon,

Please, can you help me with this problem?

In the attachment, in column E I use a search function (= INDEX (Sheet2! $ E $ 2: $ E $ 32; MAX (INDEX ((Sheet2! $ A $ 2: $ A $ 32 = C2) * (Sheet2! $ D $ 2: $ D $ 32 = D2) * ROW (Sheet2! $ D $ 2: $ D $ 32) -FILA (Sheet2! $ D $ 1);))).

I would like to load this function in that column E with a macro.

How should the programming of the macro be?

Greetings,

Ferdinan

27data-function.xlsx (34.87 KB)

On 04/27/2018 at 18:12

Hello,

The best for you is to create a Name with this Formula

Select the Cells "E2" and copy the Formula (Select it in the Formula Bar)

In the ribbon Open the Name Manager

Add a new name (By exemple SUBSTFRM) and in the ReferTo area put the Formula

Send Ok

Now you can use "= SUBSTFRM" instead of the original function.

Then in VBA you can write :

Range("E2:E10000").Formula = "=SUBSTFRM"

That's enough !

See the exemple...

25data-function-vg.xlsx (133.71 KB)

On 04/27/2018 at 21:47

Hello,

I have a file in which I have many functions and the file goes a little slow.

Which of these methods is the most efficient, so that it goes faster?

Regards,

Ferdinan

On 04/27/2018 at 22:08

I'm going to try both methods and see which is the most efficient?

Thank you very much for your help.

Regards,

Ferdinan

On 04/28/2018 at 04:54

Hello,

Realy they are the same.

Use the one that seems the easiest to program.

On 04/28/2018 at 08:11

Hello,

thank you very much for your attention

Regards,

Ferdinan