Macro vba to assgin a formula to the variable

[Minato5972]

Transformed(PRow * (c - 1) + 1, 39) = "=MAX(X" & Sum & ",Y" & Sum & ")-INDIRECT(ADDRESS(ROW(AC" & Sum + 3 & "), 28+X" & Sum + 1 & "))+ INDIRECT(ADDRESS(ROW(AF" & Sum & "), 28+(X" & Sum + 1 & "+1)))"

Value 0 must be assigned to "Transformed(PRow * (c - 1) + 1, 39) " if the value is negative.

So i tried to assign the formula to a vairably "qty" to check if the value is less than 0 by using a IF statement but worksheet.Evaluate("formula") is not working

 

[mart37]

And when you divids the formula in three pieces:
evaluate("=MAX(X" & Sum & ",Y" & Sum & ")")-evaluate("=INDIRECT(ADDRESS(ROW(AC" & Sum + 3 & "), 28+X" & Sum + 1 & "))")+evaluate("=INDIRECT(ADDRESS(ROW(AF" & Sum & "), 28+(X" & Sum + 1 & "+1)))")

 

[Minato5972]

Thanks a lot but i dont know if it worked because the columns above equation is using have formulas in them. If you can split these two i will learn from them

"=MAX(Y" & Sum + 2 & ", IF(H" & Sum & "= 99,I" & Sum & ",SUM(OFFSET(H" & Sum - 1 & ",1,12,1,-H" & Sum & "),Y" & Sum + 3 & ")/H" & Sum & ")) "
"=IF(H" & Sum & "= 6,(SUM(P" & Sum & ":T" & Sum & ")+AC" & Sum & ")/H" & Sum & ",(SUM(S" & Sum & ":T" & Sum & ")+AC" & Sum & ")/H" & Sum & ")"

Thanks a lot once again

 

[mart37]

Sorry, I don't mean the real formula in the cell but the formula to evaluate in VBA. The formula is to "big" for one evaluate function. But with three it is working.
qty=evaluate("=MAX(X" & Sum & ",Y" & Sum & ")")-evaluate("=INDIRECT(ADDRESS(ROW(AC" & Sum + 3 & "), 28+X" & Sum + 1 & "))")+evaluate("=INDIRECT(ADDRESS(ROW(AF" & Sum & "), 28+(X" & Sum + 1 & "+1)))")

 

[Minato5972]

No i mean in that formula "X & sum" depends on some other formula (it has its own) so thats the reason i cant get it working

 

[mart37]

If "sum" is a range name you can refer with: [sum]

 

[mart37]

An other approach:

Transformed(PRow * (c - 1) + 1, 39)= "=MAX(X" & Sum & ",Y" & Sum & ")-INDIRECT(ADDRESS(ROW(AC" & Sum + 3 & "), 28+X" & Sum + 1 & "))+ INDIRECT(ADDRESS(ROW(AF" & Sum & "), 28+(X" & Sum + 1 & "+1)))"
Transformed(PRow * (c - 1) + 1, 39)=IIF(Transformed(PRow * (c - 1) + 1, 39).Value<0,0,"=MAX(X" & Sum & ",Y" & Sum & ")-INDIRECT(ADDRESS(ROW(AC" & Sum + 3 & "), 28+X" & Sum + 1 & "))+ INDIRECT(ADDRESS(ROW(AF" & Sum & "), 28+(X" & Sum + 1 & "+1)))"