Translate an embedded Excel formula in vba for Excel

[Jayem877]

Hi,

I need help. I need to put this formula in Excel in vba code:

=IF(NETWORKDAYS(B4;B4)=0;0;IFERROR(INDEX(CS;MATCH(C4;CS_Position;0);MATCH(B4;CS_Date;1));IFERROR(INDEX(EC;MATCH(C4;EC_Position;0);MATCH(B4;EC_Date;1));IFERROR(INDEX(FI;MATCH(C4;FI_Position;0);MATCH(B4;FI_Date;1));IFERROR(INDEX(PA;MATCH(C4;PA_Position;0);MATCH(B4;PA_Date;1));IFERROR(INDEX(UTE;MATCH(C4;UTE_Position;0);MATCH(B4;UTE_Date;1));"Not found"))))))

Questions on my formula? Any ideas?

Thank you in advance!

Jayem

 

[davesexcel]

You can use the macro recorder to get the formula in VBA code.

start recording macro, select cell with formula and select the formula box, hit enter, stop recording, look at the code.

 

[Jayem877]

That is a pretty good idea, I should have thought about I. I'll try it tonight when I get home.

Thank you Dave!

 

[Jayem877]

Hi Dave,

I don't think it worked. Here is what it gives:

 Range("D5").Select    ActiveCell.FormulaR1C1 = _
        "=IF(NETWORKDAYS(RC[-2],RC[-2])=0,0,IFERROR(INDEX(CS,MATCH(RC[-1],CS_Position,0),MATCH(RC[-2],CS_Date,1)),IFERROR(INDEX(EC,MATCH(RC[-1],EC_Position,0),MATCH(RC[-2],EC_Date,1)),IFERROR(INDEX(FI,MATCH(RC[-1],FI_Position,0),MATCH(RC[-2],FI_Date,1)),IFERROR(INDEX(PA,MATCH(RC[-1],PA_Position,0),MATCH(RC[-2],PA_Date,1)),IFERROR(INDEX(UTE,MATCH(RC[-1],UTE_Position,0),MATCH(" & _
        "RC[-2],UTE_Date,1)),""Not found""))))))"
    Range("D6").Select

It is ok I will change the "RC-2" with variables but it gives a syntax error.

Sorry I'm new to VBA... Hope you can help!

 

[AndyMachin]

You can simply use Evaluate and paste the formula as string like this:

Range("D5").value = Evaluate("[COLOR=#333333]=IF(NETWORKDAYS(B4;B4)=0;0;IFERROR(INDEX(CS;MATCH(C4;CS_Position;0);MATCH(B4;CS_Date;1));IFERROR(INDEX(EC;MATCH(C4;EC_Position;0);MATCH(B4;EC_Date;1));IFERROR(INDEX(FI;MATCH(C4;FI_Position;0);MATCH(B4;FI_Date;1));IFERROR(INDEX(PA;MATCH(C4;PA_Position;0);MATCH(B4;PA_Date;1));IFERROR(INDEX(UTE;MATCH(C4;UTE_Position;0);MATCH(B4;UTE_Date;1));"Not found"))))))")[/COLOR]

Just change the target range as you want, maybe a variable

 

[Jayem877]

I have as a result a "#Value" but I think the evaluate function works well! Thank you AndyMachin for the tip!