Hi Jan,

Am Sun, 26 Oct 2014 04:45:24 -0700 (PDT) schrieb :

=IF(IF(ISERROR(VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),VLOOKUP($B1147,'2015p riser'!$B:$HG,DV$1,FALSE),VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE))=0,ROUND(VLOOKUP($B114 7,'2015priser'!$B:$HG,DV$2,FALSE),0),ROUND(IF(ISER ROR(VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),VLOOKUP($B1147,'2015p riser'!$B:$HG,DV$1,FALSE),VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),0))

your formula is wrong. Tell us in words what you want to calculate


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Sorry to have kept you waiting, suddenly had to Work :-(

The formula Works, when I type it in a cell, but wont be inserted via VBA.

I have to sheets, with app. 140.000 cells, from A1 to DU1140. Both sheets has Unique numbers in column A and headers in row 1. The first sheet contains actual prizes, for different items. There are only prizes in some cells, and what cells are empty and filled are not patterned in any way, its almost random.

The otherf hseet is updated with new prizes, which can be in same or other cells as in sheet 1. Now I want to insert a lookup formula in the cells, and only the cells in sheet 1, that has already numbers in them. This formula should look up prizes in sheet 2, if such exist and return them to sheet 1, if the corresponding cell in sheet one is not empty. If it is, should not be inserted.

As I mentioned the formula works if I type it, but I can'øt see why the VBA code suddenly fails, when it works with other, shorter formulas.

Jan

Den søndag den 26. oktober 2014 13.11.38 UTC+1 skrev Claus Busch:
Hi Jan,

Am Sun, 26 Oct 2014 04:45:24 -0700 (PDT) schrieb
:

=IF(IF(ISERROR(VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),VLOOKUP($B1147,'2015p riser'!$B:$HG,DV$1,FALSE),VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE))=0,ROUND(VLOOKUP($B114 7,'2015priser'!$B:$HG,DV$2,FALSE),0),ROUND(IF(ISER ROR(VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),VLOOKUP($B1147,'2015p riser'!$B:$HG,DV$1,FALSE),VLOOKUP($B1147,'JK-prisfil'!$C:$HG,DV$1,FALSE)),0))

your formula is wrong. Tell us in words what you want to calculate


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Jan,

Am Mon, 27 Oct 2014 15:28:04 -0700 (PDT) schrieb :

I have to sheets, with app. 140.000 cells, from A1 to DU1140. Both sheets has Unique numbers in column A and headers in row 1. The first sheet contains actual prizes, for different items. There are only prizes in some cells, and what cells are empty and filled are not patterned in any way, its almost random.

The otherf hseet is updated with new prizes, which can be in same or other cells as in sheet 1. Now I want to insert a lookup formula in the cells, and only the cells in sheet 1, that has already numbers in them. This formula should look up prizes in sheet 2, if such exist and return them to sheet 1, if the corresponding cell in sheet one is not empty. If it is, should not be inserted.

a IF formula needs 3 arguments and the structure is
=IF(Condition; True Part; False Part)

A nested IF formula looks like
IF(Condition1;True Part; IF(Condition2;True Part;IF(Condition3;True Part;False Part)))

To get the correct result the conditions must be listed from highest
priority to the lowest.

What is your order of priority? And what Excel version do you use?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional