Gilbfernandes

New Member
Joined
Jan 26, 2016
Messages
20
Hi Everyone,

I'm trying to enter a formula in a given cell indicated by Range("P" & LastRow + 1), but for some reason it's not working.
If I enter the formula manually at the range, it works perfectly. That is the formula :

=IFERROR(IF(O65>0, _
IF('56'!$E$11=3000,DAYS360(TODAY(),O65+12*30), _
IF('56'!$E$11=3500,DAYS360(TODAY(),O65+14*30), _
IF('56'!$E$11=4000,DAYS360(TODAY(),O65+16*30), _
IF('56'!$E$11=4500,DAYS360(TODAY(),O65+18*30), _
IF('56'!$E$11=5000,DAYS360(TODAY(),O65+20*30), _
IF('56'!$E$11=5500,DAYS360(TODAY(),O65+22*30), _
IF('56'!$E$11=6000,DAYS360(TODAY(),O65+24*30),"")))))))),"")

The way I am entering it with VBA is:

Range("P" & LastRow + 1) = "=IFERROR(IF(O" & LastRow + 1 & ">0," _
& "IF('" & LinkNumber & "'!$E$11=3000,DAYS360(TODAY(),O" & LastRow + 1 & "+12*30)," _
& "IF('" & LinkNumber & "'!$E$11=3500,DAYS360(TODAY(),O" & LastRow + 1 & "+14*30)," _
& "IF('" & LinkNumber & "'!$E$11=4000,DAYS360(TODAY(),O" & LastRow + 1 & "+16*30)," _
& "IF('" & LinkNumber & "'!$E$11=4500,DAYS360(TODAY(),O" & LastRow + 1 & "+18*30)," _
& "IF('" & LinkNumber & "'!$E$11=5000,DAYS360(TODAY(),O" & LastRow + 1 & "+20*30)," _
& "IF('" & LinkNumber & "'!$E$11=5500,DAYS360(TODAY(),O" & LastRow + 1 & "+22*30)," _
& "IF('" & LinkNumber & "'!$E$11=6000,DAYS360(TODAY(),O" & LastRow + 1 & "+24*30),"")))))))),"")"

But it is not working. Could someone tell me why it is not working ???

Thank you.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
These quotes
Code:
),"")))))))),"")"
need to be doubled up
Code:
),"""")))))))),"""")"
, also add
Code:
.Formula
to the end of the range
Code:
Range("P" & LastRow + 1).Formula
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Firstly, (I think) your formula could be condensed to:
Code:
=IF(AND('56'!$E$11>=3000,'56'!$E$11<=6000,MOD('56'!$E$11,500)=0),DAYS360(TODAY(),O65+(12+('56'!$E$11-3000)/500*60)),"")

Secondly, the double quotes in the final argument of the IF function need to be doubled up.
 

Gilbfernandes

New Member
Joined
Jan 26, 2016
Messages
20
These quotes
Code:
),"")))))))),"")"
need to be doubled up
Code:
),"""")))))))),"""")"
, also add
Code:
.Formula
to the end of the range
Code:
Range("P" & LastRow + 1).Formula

Thanks Fluff, it worked now.


Thanks njimack, I will try your idea for sure, it would be much simpler like this.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,335
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top