VLOOKUP function is changed after pasting

[Paul Meems]

I use a template Excel sheet with on the second row for some columns formulas.

I first fill in the columns with data from a db and then copy the formulas for the columns.

This works fine, except when the formula contains VLOOKUP, then the last argument (default value) is lost, resulting in an 'N/A'.

This is part of my C# code:

                // Update formulas:

                var columnsWithFormula = new[] { "H", "I", "K", "M", "Q", "R", "S", "T", "U", "V" };

                foreach (var columnWithFormula in columnsWithFormula)

                {

                    worksheet.Range[$"{columnWithFormula}{startRow}:{columnWithFormula}{row}"].Formula = worksheet.Range[$"{columnWithFormula}{startRow}"].Formula;

                }

This formula works as expected:

=IF(AND(I8="N";Q8="J";R8="N");T8* K8; 0)

This one fails:

=IF(AND(Q8="J";R8="J"); MIN(T8*J8; VLOOKUP(T8; Informatie!$C$3:$D$10;2;0) ); 0)

Instead of pasting the exact formula, this is pasted:

=IF(AND(Q8="J";R8="J"); MIN(T8*J8; VLOOKUP(T8; Informatie!$C$3:$D$10;2) ); 0)

Notice the missing ;0 after ;2

Now my worksheet produces wrong values. When I manually change the formula in Excel all works fine.

What do I need to do to let xlsIO copy my formula without altering it?

Thanks,

Paul

[Paul Meems]

I did some more testing and it is definitially a problem with the VLOOKUP formula.

I simplified the formula.

This needs to be pasted: 

=VLOOKUP(T8; Informatie!$C$3:$D$10;2;0)

and this is what actually is pasted: 

=VLOOKUP(T8; Informatie!$C$3:$D$10;2)

Again with the missing 3rd argument.

[Dilli Babu Nandha Gopal]

Hi Paul, 

Thank you for contacting Syncfusion support. 

We have tried to reproduce the reported issue from our side, but the VLOOKUP formula is properly retrieved from a worksheet and assigned to another cell. We have shared the sample for your reference which can be downloaded from the following link. 

http://www.syncfusion.com/downloads/support/forum/139653/ze/VlookUP-707458005  

Kindly modify the sample to reproduce the issue and share us the same. Please provide us your current Essential Studio version in which issue is reproduced.  

Regards, 

Dilli babu. 

[Paul Meems]

Thanks for your sample.

Using your code I can't reproduce either, but after comparing I found the problem.

It is in workbook.SetSeparators(',', ';');

When I add this line to your code the VLOOKUP formula is pasted wrongly, the last argument is missing again.

I need to set the separators because the template is made using Dutch Excel and without it, I get errors in other parts of my code.

I've attached my template as a reference. The VLOOKUP formula is column 'V' of the first sheet.

I'm using v16.1460.0.24

Attachment: Syncfusion_83a82d79.7z

[Dilli Babu Nandha Gopal]

Hi Paul, 

 

Thank you for your patience. 

 

We can reproduce the reported issue with “VLOOKUP formula is improperly pasted while setting Workbook separator” and confirmed it as a defect. The fix for this issue will be included in our Essential Studio 2018 Volume 3 SP1 release which is estimated to be available by October, 2018 tentatively. 

 

Regards, 

Dilli babu. 

[Paul Meems]

Thanks for the follow-up and confirming it is a defect.

For now, we use IF AND statements to get the same result.

[Dilli Babu Nandha Gopal]

Hi Paul, 

Thank you for your update. 

Yes, you can proceed to use IF AND statements as a workaround until our Essential Studio 2018 Volume 3 SP1 release which is estimated to be available by October, 2018 tentatively. 

Please let us know if you need any further assistance on this. 

Regards, 

Dilli babu.