Debug Error on Macro

[Greg Bonaparte]

Does any know why I get Debugged in the following macro. I copied the syntax directly from a video tutorial and have double checked that it appears correct.  The the first debug (yellow highlight) appears on the 1st line starting with "Range...". 

Sub Enter_Formulas()

Range("$AZ30").Formula = "=IF(($S30 > 0)*($R30 > 0), $U30=$S30,"")"
Range("$AZ31").Formula = "=IF(($S31 > 0)*($R31 > 0), $U31=$S31,"")"
Range("$AZ32").Formula = "=IF(($S32 > 0)*($R32 > 0), $U32=$S32,"")"
Range("$AZ33").Formula = "=IF(($S33 > 0)*($R33 > 0), $U33=$S33,"")"
Range("$AZ34").Formula = "=IF(($S34 > 0)*($R34 > 0), $U34=$S34,"")"
Range("$AZ35").Formula = "=IF(($S35 > 0)*($R35 > 0), $U35=$S35,"")"
Range("$AZ36").Formula = "=IF(($S36 > 0)*($R36 > 0), $U36=$S36,"")"
Range("$AZ37").Formula = "=IF(($S37 > 0)*($R37 > 0), $U37=$S37,"")"
Range("$AZ38").Formula = "=IF(($S38 > 0)*($R38 > 0), $U38=$S38,"")"
Range("$AZ39").Formula = "=IF(($S39 > 0)*($R39 > 0), $U39=$S39,"")"

Range("$AZ40").Formula = "=IF(($S40 > 0)*($R40 > 0), $U40=$S40,"")"
Range("$AZ41").Formula = "=IF(($S41 > 0)*($R41 > 0), $U41=$S41,"")"
Range("$AZ42").Formula = "=IF(($S42 > 0)*($R42 > 0), $U42=$S42,"")"
Range("$AZ43").Formula = "=IF(($S43 > 0)*($R44 > 0), $U43=$S43,"")"
Range("$AZ44").Formula = "=IF(($S44 > 0)*($R44 > 0), $U44=$S44,"")"
Range("$AZ45").Formula = "=IF(($S45 > 0)*($R45 > 0), $U45=$S45,"")"
Range("$AZ46").Formula = "=IF(($S46 > 0)*($R46 > 0), $U46=$S46,"")"
Range("$AZ47").Formula = "=IF(($S47 > 0)*($R47 > 0), $U47=$S47,"")"
Range("$AZ48").Formula = "=IF(($S48 > 0)*($R48 > 0), $U48=$S48,"")"
Range("$AZ49").Formula = "=IF(($S49 > 0)*($R49 > 0), $U49=$S49,"")"

Range("$AZ50").Formula = "=IF(($S50 > 0)*($R50 > 0), $U50=$S50,"")"
Range("$AZ51").Formula = "=IF(($S51 > 0)*($R51 > 0), $U51=$S51,"")"
Range("$AZ52").Formula = "=IF(($S52 > 0)*($R52 > 0), $U52=$S52,"")"
Range("$AZ53").Formula = "=IF(($S53 > 0)*($R53 > 0), $U53=$S53,"")"
Range("$AZ54").Formula = "=IF(($S54 > 0)*($R54 > 0), $U54=$S54,"")"
Range("$AZ55").Formula = "=IF(($S55 > 0)*($R55 > 0), $U55=$S55,"")"
Range("$AZ56").Formula = "=IF(($S56 > 0)*($R56 > 0), $U56=$S56,"")"
Range("$AZ57").Formula = "=IF(($S57 > 0)*($R57 > 0), $U57=$S57,"")"
Range("$AZ58").Formula = "=IF(($S58 > 0)*($R58 > 0), $U58=$S58,"")"
Range("$AZ59").Formula = "=IF(($S59 > 0)*($R59 > 0), $U59=$S59,"")"

Range("$AZ60").Formula = "=IF(($S60 > 0)*($R60 > 0), $U60=$S60,"")"
Range("$AZ61").Formula = "=IF(($S61 > 0)*($R61 > 0), $U61=$S61,"")"
Range("$AZ62").Formula = "=IF(($S62 > 0)*($R62 > 0), $U62=$S62,"")"
Range("$AZ63").Formula = "=IF(($S63 > 0)*($R63 > 0), $U63=$S63,"")"
Range("$AZ64").Formula = "=IF(($S64 > 0)*($R64 > 0), $U64=$S64,"")"
Range("$AZ65").Formula = "=IF(($S65 > 0)*($R65 > 0), $U65=$S65,"")"
Range("$AZ66").Formula = "=IF(($S66 > 0)*($R66 > 0), $U66=$S66,"")"
Range("$AZ67").Formula = "=IF(($S67 > 0)*($R67 > 0), $U67=$S67,"")"
Range("$AZ68").Formula = "=IF(($S68 > 0)*($R68 > 0), $U68=$S68,"")"
Range("$AZ69").Formula = "=IF(($S69 > 0)*($R69 > 0), $U69=$S69,"")"

Range("$AZ70").Formula = "=IF(($S70 > 0)*($R70 > 0), $U70=$S70,"")"
Range("$AZ71").Formula = "=IF(($S71 > 0)*($R71 > 0), $U71=$S71,"")"
Range("$AZ72").Formula = "=IF(($S72 > 0)*($R72 > 0), $U72=$S72,"")"
Range("$AZ73").Formula = "=IF(($S73 > 0)*($R73 > 0), $U73=$S73,"")"
Range("$AZ74").Formula = "=IF(($S74 > 0)*($R74 > 0), $U74=$S74,"")"
Range("$AZ75").Formula = "=IF(($S75 > 0)*($R75 > 0), $U75=$S75,"")"
Range("$AZ76").Formula = "=IF(($S76 > 0)*($R76 > 0), $U76=$S76,"")"
Range("$AZ77").Formula = "=IF(($S77 > 0)*($R77 > 0), $U77=$S77,"")"
Range("$AZ78").Formula = "=IF(($S78 > 0)*($R78 > 0), $U78=$S78,"")"
Range("$AZ79").Formula = "=IF(($S79 > 0)*($R79 > 0), $U79=$S79,"")"

Range("$AZ80").Formula = "=IF(($S80 > 0)*($R80 > 0), $U80=$S80,"")"
Range("$AZ81").Formula = "=IF(($S81 > 0)*($R81 > 0), $U81=$S81,"")"
Range("$AZ82").Formula = "=IF(($S82 > 0)*($R82 > 0), $U82=$S82,"")"
Range("$AZ83").Formula = "=IF(($S83 > 0)*($R83 > 0), $U83=$S83,"")"
Range("$AZ84").Formula = "=IF(($S84 > 0)*($R84 > 0), $U84=$S84,"")"
Range("$AZ85").Formula = "=IF(($S85 > 0)*($R85 > 0), $U85=$S85,"")"
Range("$AZ86").Formula = "=IF(($S86 > 0)*($R86 > 0), $U86=$S86,"")"
Range("$AZ87").Formula = "=IF(($S87 > 0)*($R87 > 0), $U87=$S87,"")"
Range("$AZ88").Formula = "=IF(($S88 > 0)*($R88 > 0), $U88=$S88,"")"
Range("$AZ89").Formula = "=IF(($S89 > 0)*($R89 > 0), $U89=$S89,"")"

Range("$AZ90").Formula = "=IF(($S90 > 0)*($R90 > 0), $U90=$S90,"")"
Range("$AZ91").Formula = "=IF(($S91 > 0)*($R91 > 0), $U91=$S91,"")"
Range("$AZ92").Formula = "=IF(($S92 > 0)*($R92 > 0), $U92=$S92,"")"
Range("$AZ93").Formula = "=IF(($S93 > 0)*($R93 > 0), $U93=$S93,"")"
Range("$AZ94").Formula = "=IF(($S94 > 0)*($R94 > 0), $U94=$S94,"")"
Range("$AZ95").Formula = "=IF(($S95 > 0)*($R95 > 0), $U95=$S95,"")"
Range("$AZ96").Formula = "=IF(($S96 > 0)*($R96 > 0), $U96=$S96,"")"
Range("$AZ97").Formula = "=IF(($S97 > 0)*($R97 > 0), $U97=$S97,"")"
Range("$AZ98").Formula = "=IF(($S98 > 0)*($R98 > 0), $U98=$S98,"")"
Range("$AZ99").Formula = "=IF(($S99 > 0)*($R99 > 0), $U99=$S99,"")"

End Sub

[#2]

Problem found but not resolved. In the original formula q30 is round down to the nearest zero. I your formula q30,0 become q r without zero. Can you rephrase syntax to include zero?

[#3]

Problem found but not resolved. In the original formula q30 is round down to the nearest zero. In your formula q30,0 become q r without zero. Can you rephrase syntax to include zero?

[#4]

Follow up:

I see that somehow in the conversation my original roundown formula was changed. This is what it is outside of the macro:

=ROUNDDOWN(($Y$17-$Y$18)/$Q30,0)

[#5]

Hmm, Can you write the formula that rounds down to zero so I can see the full syntax. 

[#6]

Please see the attachment for the syntax of ROUNDDOWN

[#7]

Thanks so much for your assistance Man Fai.  With your help I was able to come really close to the end of a 4 year project. I have removed ROUNDDOWN from the macro and moved it to within cells. I believe I can simplify the process by using SUM instead. However I get a syntax error. See macro below:

Sub Enter_Formulas()

For r = 30 To 37
SUM($Y$17-$Y$18)" & Chr(34) & Chr(34) & ")"
Next r

End Sub

[#8]

I was able t fix the syntax with the following: But troubles remain. Cells are conflicting within the spreadsheet. So my issue is not with the macro. Ill need to investigate the issues outside of macro. Thanks again for you help.

Sub Enter_Formulas()

For r = 30 To 37
Range("$U" & r).Formula = "=SUM($Y$17-$Y$18)"

Next r

End Sub

[#9]

---

@Greg Bonaparte wrote:

I was able t fix the syntax with the following: But troubles remain. Cells are conflicting within the spreadsheet. So my issue is not with the macro. Ill need to investigate the issues outside of macro. Thanks again for you help.

Sub Enter_Formulas()

For r = 30 To 37
Range("$U" & r).Formula = "=SUM($Y$17-$Y$18)"

Next r

End Sub

---

I wonder why you use SUM-function but have the minus sign included. Moreover, if you want to sum two cells, it would be better to use "=Y17+Y18". If you want to sum more cells, SUM-function is useful (E.g. SUM(Y17:Y28)).

[#10]

Thanks Man Fai

Turns out I went back to ROUNDDOWN

Sub Enter_Formulas()

For r = 30 To 33
Range("$U" & r).Formula = "=IF(($S" & r & " > 0)*($R" & r & " > 0.00001), ROUNDDOWN(($Y$17-$Y$18)/$Q" & r & ",0)," & Chr(34) & Chr(34) & ")"
Next r

End Sub

Above macro works but I get conflict from  cell Y18  indirectly conflicting with S30, U30 & V30. I'm wondering if there is a way to tell the macro to disregard the conflict. A value in the "NEXT r" will be modified when "this r" is processed which I believe is the indirect conflict.

[#11]

Personally , since the cells seem to be contiguous as a starter I would use a single expression:
Range("AZ30:$AZ99").Formula = [Here use the first formula at the top]
This expression will automatically copy the formula in each cell as relative formulas.
Please look at the double quotation marks at the end. As in ..... $U99=$S99,"")"
Are you sure they're ok?
While I'm looking at the formula, since they are all copied down in column AZ, you do not need to $ in the reference.
Daniel