PDA

View Full Version : Solved: Help to copy the formula



VISHAL120
04-14-2012, 03:53 PM
Hi ,
Am trying to used this code to be place in a range but a having an error like this :

Application-defined or object-defined error

here is my code :

Sub OverBook_Formula()
starttime = Timer
Dim lLR As Long

Sheets("loading").Activate
ActiveSheet.Unprotect

LR = Cells(Rows.Count, "D").End(xlUp).Row


With ThisWorkbook.Sheets("loading").Range("AA53:AY" & LR)

.Formula = "=IF(OR($H53="",$R53="",$M53=""),0,IF(AA$51<$L53,0,IF(AA$51>=$L53,IF(0<($S53),MIN(($S53-SUM(Z53:$Z53)),$T53,SUMIF($Y$3:$Y$20,$Y53,AA$3:AA$20)))))) "




End With





End Sub

can i please have a guide on that.
thanks.

mancubus
04-14-2012, 04:56 PM
hi.



Sub OverBook_Formula()

Dim LR As Long

starttime = Timer

With Sheets("loading")
.Unprotect
LR = .Cells(Rows.Count, "D").End(xlUp).Row
.Range("AA53:AY" & LR).Formula = "=IF(OR($H53="""",$R53="""",$M53=""""),0,IF(AA$51<$L53,0,IF(AA$51>=$L53,IF(0<($S53),MIN(($S53-SUM(Z53:$Z53)),$T53,SUMIF($Y$3:$Y$20,$Y53,AA$3:AA$20))))))"
End With

End Sub

shrivallabha
04-15-2012, 12:19 AM
1. You can use macro recorder. And write your formula in Excel. VBA will give R1C1 style formula. Its readability could be an issue as we are more used to reading A1 styled references more.

2. The other option is copy the formula from Excel and paste it in VBA.
=IF(OR($H53="",$R53="",$M53=""),0,IF(AA$51<$L53,0,IF(AA$51>=$L53,IF(0<($S53),MIN(($S53-SUM(Z53:$Z53)),$T53,SUMIF($Y$3:$Y$20,$Y53,AA$3:AA$20))))))
Select this string and then do find and replace [Toggle on "Selected Text" option]:
FIND : "
REPLACE : ""
and then wrap it with double quotes. It will look like the formula Macubus provided.
.Formula = "=IF(OR($H53="""",$R53="""",$M53=""""),0,IF(AA$51<$L53,0,IF(AA$51>=$L53,IF(0<($S53),MIN(($S53-SUM(Z53:$Z53)),$T53,SUMIF($Y$3:$Y$20,$Y53,AA$3:AA$20))))))"

VISHAL120
04-15-2012, 09:55 PM
hi Both,

thanks a lot i have understand it now that it shall have always double quotes.
it solved.

mnay thanks again.