Write Nested Function Into Excel Using VBA

Question

I'm trying to write the following nested formulas into cell CY6 using VBA:

=IF(AND(CV6>MAX(BJ40,BJ5), CV6/MAX(BJ40,BJ5)>1.199999)," Moment Resistance Mr > Factored Moment Mf =" & ROUND(MAX(BJ40,BJ5),2)  & ". Mr/Mf =" & ROUND(CV6/MAX(BJ40,BJ5),2) & ". Proceed to Crack Control Step =>", " Moment Ratio Mr/Mf is Smaller than 1.2, Increase Steel Area. Mr/Mf=" & ROUND(CV6/BJ40,2))

The code I tried to use is simple

Range("CY6").value = " =IF(AND(CV6>MAX(BJ40,BJ5), CV6/MAX(BJ40,BJ5)>1.199999)," Moment Resistance Mr > Factored Moment Mf =" & ROUND(MAX(BJ40,BJ5),2)  & ". Mr/Mf =" & ROUND(CV6/MAX(BJ40,BJ5),2) & ". Proceed to Crack Control Step =>", " Moment Ratio Mr/Mf is Smaller than 1.2, Increase Steel Area. Mr/Mf=" & ROUND(CV6/BJ40,2)) "

I also tried the same thing using

worksheets("sheet1").range("CY6").formula= "...."

However, it returns a "compile error expected: end of statement" and highlights the word moment (the first one) I think it's because of the quotation marks but I am not sure how it is supposed to work.

To add comments and the sort, you need to use an ampersand between info, e.g.: range("cy6").formula = "=if(cond=cond,1,0)" & " words"; for additional, embedded words, you need to open/close quotations, like "=if(cond=cond,""True"",""False"")" — Cyril
– Cyril, Commented Mar 29, 2019 at 16:17
(1) Double all your quote characters within a string so that the VBA compiler knows that you want a quote character and not end your string. (2) User Range("CY6").formula to assign a formula, not .value — FunThomas
– FunThomas, Commented Mar 29, 2019 at 16:19

Cyril · Accepted Answer · 2019-03-29 16:20:57Z

1

Let's see about this...

=IF(AND(CV6>MAX(BJ40,BJ5), CV6/MAX(BJ40,BJ5)>1.199999)," Moment Resistance Mr > Factored Moment Mf =" & ROUND(MAX(BJ40,BJ5),2) & ". Mr/Mf =" & ROUND(CV6/MAX(BJ40,BJ5),2) & ". Proceed to Crack Control Step =>", " Moment Ratio Mr/Mf is Smaller than 1.2, Increase Steel Area. Mr/Mf=" & ROUND(CV6/BJ40,2))

range("cy6").formula = "=IF(AND(CV6>MAX(BJ40,BJ5), CV6/MAX(BJ40,BJ5)>1.199999),"" Moment Resistance Mr > Factored Moment Mf ="" & ROUND(MAX(BJ40,BJ5),2) & "". Mr/Mf ="" & ROUND(CV6/MAX(BJ40,BJ5),2) & "". Proceed to Crack Control Step =>"", "" Moment Ratio Mr/Mf is Smaller than 1.2, Increase Steel Area. Mr/Mf="" & ROUND(CV6/BJ40,2))"

answered Mar 29, 2019 at 16:20

Cyril

6,8291 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mohamed R Over a year ago

OMG you guys are so fast! I thought I'd have to come back tomorrow, but no 10min. Thank you very very much!

Cyril Over a year ago

@MohamedR did this work for you? if so, please mark as Answered (check mark on left of post) so this is labeled appropriately in searches

Collectives™ on Stack Overflow

Write Nested Function Into Excel Using VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related