Instead of pushing fomrula into the cell i want to push value by evaluating

[Minato5972]

Transformed(PRow * (c - 1) + 1, 29) = "=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 & ")) "
Formula is getting pushed and code is running properly
But i want to evaluate the formula and push in value. I tried,
Transformed(PRow * (c - 1) + 1, 29) = Evaluate("=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 & ")) ")
To check if it really evaluating i tried this but its not working.
Dim a as Long
a = Evaluate("=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 & ")) ")
MsgBox a

I am getting type mismatch error

 

[Anthony47]

Transformed(PRow * (c - 1) + 1, 29) = "=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 & ")) "
Formula is getting pushed and code is running properly

Well, if it works then you might simply add this additional line:

Transformed(PRow * (c - 1) + 1, 29) = "=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 & ")) "
Transformed(PRow * (c - 1) + 1, 29).Value = Transformed(PRow * (c - 1) + 1, 29).Value          '<<< ADD THIS LINE

Bye