Translate an embedded Excel formula in vba for Excel

[Jayem877]

Hi folks,

Could anyone help me translate the following Excel formula to VBA:

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

Where:
-CS, CS_Position, EC, EC_Position, etc. are referenced names I put in Excel.
-B5, C5 are my cells in Excel.

Recording a macro did not give probing results.

Any thoughts?
Any help is appreciated!

JayEm

 

[alansidman]

Translating Excel formulas to VBA are not always the way to go when attempting to create VBA functionality. It is better if you give a scenario of what you are attempting to achieve. Explain it to us as if we are standing in line at a coffee shop and you are explaining it. In this manner, we may be able to provide a solid VBA solution that suits your specific needs.

 

[Jayem877]

Here would be my story then (sorry English is my second language, let me know if something is not clear):

The file is used to calculate salary pay. So I have multiple tabs, each tabs contains 1 collective agreement. On 1 tab, the type of jobs are on one column, let's say "CS_Position" and the dates are on one row "CS_Date". Names in parenthesys are named range by the way. The corresponding salary are in a matrix, so basically the layout is ready like a vlookup formula. Except that my embedded formula 'Index' with 'match' picks up the column automatically.

So on the primary tab, I have on one cell "C5", where the job position is, let's call it "EC job level 2" and on cell "B5" the date, let's call it "2016-08-01". So the formula above (located in cell "D5") looks for the "EC job level 2" in name ranged "CS_Position" first, in that case, returns an error, then the formula continues, it goes look in named ranged "EC_Position", (finds it!), and with the date, returns the corresponding salary at the intersection of the "EC_Position" and "EC_Date", like a VLookup!

I want that logic in Excel VBA. Does it helps? Let me know if you need more details.