Convert Formula to VBA

[joand]

I was wondering how to convert this formula to VBA. Anyone got ideas?


{=SUM(IF(Sheet1!$C$4:$C$1118=Sheet2!$B5,IF(Sheet1!$D$4:$D$1118=Sheet2!C$2,IF(Sheet1!$E$4:$E$1118=1,1,0))))}

 

[Andrew Poulsom]

What do you mean by "convert to VBA"? I don't think a custom function would be any quicker.

 

[joand]

how can i convert that to custom function Andrew?

 

[jim may]

The simpliest (and most amazing) way is to with the current formula cell selected
Turn on Record a Macro
Once you do simply (back on the worksheet) Press the F2 key (to go into Edit Mode);
then Immediately press enter to get out of Edit Mode.
Stop the Macro

Goto your VBE and view the code created...

Jim

 

[jim may]

In the case of an Array formula you will need to replace the

ActiveCell.FormulaR1C1 =

with

ActiveCell.FormulaArray =

Jim

 

[Andrew Poulsom]

how can i convert that to custom function Andrew?

Why do you want a custom function when you already have built in functions that do the job?

 

[Boller]

In the case of an Array formula you will need to replace the

ActiveCell.FormulaR1C1 =

with

ActiveCell.FormulaArray =

Jim

.

Or with the macro recorder on :- Ctrl+Shift+Enter

 

[jim may]

Yeah Boller - half-asleep here!!
Tks,

 

[joand]

Why do you want a custom function when you already have built in functions that do the job?

i just want to get the value, and not the formula in itself.

 

[joand]

thanks boller and jim for your ideas.