SOLVED

Debug Error on Macro

Iron Contributor

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

29 Replies

Additional Info:

The error says:

Run-time error '1004':

Application-defined or object-defined error

It's due to the use of "" in your formula which the VBA code sees as the end of the string.

Easiest fix is to replace "" with 0

Thanks for your reply Wyn.

Just to clarify, Are you saying every line should look like this:

Range("$AZ30").Formula = "=IF(($S30 > 0)*($R30 > 0), $U30=$S30,"")0

with the last line looking like this:

Range("$AZ99").Formula = "=IF(($S99 > 0)*($R99 > 0), $U99=$S99,"")"

 

When I attempted to do this

Range("$AZ30").Formula = "=IF(($S30 > 0)*($R30 > 0), $U30=$S30,"")0

every line self populated with this

Range("$AZ99").Formula = "=IF(($S99 > 0)*($R99 > 0), $U99=$S99,"")0"

 

adding the quote sign automatically.

Hi @Greg Bonaparte 

 

No I mean this sorry

 

Range("$AZ30").Formula = "=IF(($S30 > 0)*($R30 > 0), $U30=$S30,0)"

Thank you Wyn, The debug error went away. However instead of getting the value in u30 to equal the value is s30, I get a "false" message in az30.

I think you want to type a formula in excel like the following:
            = IF(Criteria,1,"")

The cell will be blank when the criteria is not true. 

 

However, the use of "" in VBA in a string will be viewed as one quotation (in order to distinguish with the string end). So you need to change the VBA statement as
          Range("$AZ30").Formula = "=IF(($S30 > 0)*($R30 > 0), $U30=$S30,"""")"

 

You may also try chr(34) for the use of quotation mark in .formula.

        Range("$AZ30").formula = "=IF(($S30 > 0)*($R30 > 0), $U30 = $S30," & chr(34) & chr(34) & ")"

 

Hope that it is helpful.

Thank you Man Fai Chan,

I tried both suggestions. Both formulas left a dashs "---" in place of the "false". 

 

So your formula checks if $U30=$S30, if they don't match it will therefore return FALSE

If you're hoping for something different maybe post a sample file with an explanation of what you need

Thanks
Hi Wyn thanks for your reply. In this formula I am trying to copy whatever value is found in S30, copy to U30
Hi Greg

A formula cannot put a value in a different cell.

Sounds a bit like you need the macro to run down the list of values and perform some actions based on tests. Unfortunately your formula based approach won’t work

If you can attach a sample file with some instructions on what you are wanting to do I can see if I can provide you with the appropriate code

I have updated the formula to reflect the ultimate goal. I've tried all suggestions above. I get debug error with each. See final code:

 

Sub Enter_Formulas()

Range("$U30").Formula = "=IF(($S30 > 0)*($R30 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q30," & Chr(34) & Chr(34) & ")"
Range("$U31").Formula = "=IF(($S31 > 0)*($R31 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q31," & Chr(34) & Chr(34) & ")"
Range("$U32").Formula = "=IF(($S32 > 0)*($R32 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q32," & Chr(34) & Chr(34) & ")"
Range("$U33").Formula = "=IF(($S33 > 0)*($R33 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q33," & Chr(34) & Chr(34) & ")"
Range("$U34").Formula = "=IF(($S34 > 0)*($R34 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q34," & Chr(34) & Chr(34) & ")"
Range("$U35").Formula = "=IF(($S35 > 0)*($R35 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q35," & Chr(34) & Chr(34) & ")"
Range("$U36").Formula = "=IF(($S36 > 0)*($R36 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q36," & Chr(34) & Chr(34) & ")"
Range("$U37").Formula = "=IF(($S37 > 0)*($R37 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q37," & Chr(34) & Chr(34) & ")"
Range("$U38").Formula = "=IF(($S38 > 0)*($R38 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q38," & Chr(34) & Chr(34) & ")"
Range("$U39").Formula = "=IF(($S39 > 0)*($R39 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q39," & Chr(34) & Chr(34) & ")"

Range("$U40").Formula = "=IF(($S40 > 0)*($R40 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q40," & Chr(34) & Chr(34) & ")"
Range("$U41").Formula = "=IF(($S41 > 0)*($R41 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q41," & Chr(34) & Chr(34) & ")"
Range("$U42").Formula = "=IF(($S42 > 0)*($R42 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q42," & Chr(34) & Chr(34) & ")"
Range("$U43").Formula = "=IF(($S43 > 0)*($R44 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q43," & Chr(34) & Chr(34) & ")"
Range("$U44").Formula = "=IF(($S44 > 0)*($R44 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q44," & Chr(34) & Chr(34) & ")"
Range("$U45").Formula = "=IF(($S45 > 0)*($R45 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q45," & Chr(34) & Chr(34) & ")"
Range("$U46").Formula = "=IF(($S46 > 0)*($R46 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q46," & Chr(34) & Chr(34) & ")"
Range("$U47").Formula = "=IF(($S47 > 0)*($R47 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q47," & Chr(34) & Chr(34) & ")"
Range("$U48").Formula = "=IF(($S48 > 0)*($R48 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q48," & Chr(34) & Chr(34) & ")"
Range("$U49").Formula = "=IF(($S49 > 0)*($R49 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q49," & Chr(34) & Chr(34) & ")"

Range("$U50").Formula = "=IF(($S50 > 0)*($R50 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q50," & Chr(34) & Chr(34) & ")"
Range("$U51").Formula = "=IF(($S51 > 0)*($R51 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q51," & Chr(34) & Chr(34) & ")"
Range("$U52").Formula = "=IF(($S52 > 0)*($R52 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q52," & Chr(34) & Chr(34) & ")"
Range("$U53").Formula = "=IF(($S53 > 0)*($R53 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q53," & Chr(34) & Chr(34) & ")"
Range("$U54").Formula = "=IF(($S54 > 0)*($R54 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q55," & Chr(34) & Chr(34) & ")"
Range("$U55").Formula = "=IF(($S55 > 0)*($R55 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q56," & Chr(34) & Chr(34) & ")"
Range("$U56").Formula = "=IF(($S56 > 0)*($R56 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q57," & Chr(34) & Chr(34) & ")"
Range("$U57").Formula = "=IF(($S57 > 0)*($R57 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q58," & Chr(34) & Chr(34) & ")"
Range("$U58").Formula = "=IF(($S58 > 0)*($R58 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q59," & Chr(34) & Chr(34) & ")"
Range("$U59").Formula = "=IF(($S59 > 0)*($R59 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q60," & Chr(34) & Chr(34) & ")"

Range("$U60").Formula = "=IF(($S60 > 0)*($R60 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q60," & Chr(34) & Chr(34) & ")"
Range("$U61").Formula = "=IF(($S61 > 0)*($R61 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q61," & Chr(34) & Chr(34) & ")"
Range("$U62").Formula = "=IF(($S62 > 0)*($R62 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q62," & Chr(34) & Chr(34) & ")"
Range("$U63").Formula = "=IF(($S63 > 0)*($R63 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q63," & Chr(34) & Chr(34) & ")"
Range("$U64").Formula = "=IF(($S64 > 0)*($R64 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q64," & Chr(34) & Chr(34) & ")"
Range("$U65").Formula = "=IF(($S65 > 0)*($R65 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q65," & Chr(34) & Chr(34) & ")"
Range("$U66").Formula = "=IF(($S66 > 0)*($R66 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q66," & Chr(34) & Chr(34) & ")"
Range("$U67").Formula = "=IF(($S67 > 0)*($R67 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q67," & Chr(34) & Chr(34) & ")"
Range("$U68").Formula = "=IF(($S68 > 0)*($R68 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q68," & Chr(34) & Chr(34) & ")"
Range("$U69").Formula = "=IF(($S69 > 0)*($R69 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q69," & Chr(34) & Chr(34) & ")"

Range("$U70").Formula = "=IF(($S70 > 0)*($R70 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q70," & Chr(34) & Chr(34) & ")"
Range("$U71").Formula = "=IF(($S71 > 0)*($R71 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q71," & Chr(34) & Chr(34) & ")"
Range("$U72").Formula = "=IF(($S72 > 0)*($R72 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q72," & Chr(34) & Chr(34) & ")"
Range("$U73").Formula = "=IF(($S73 > 0)*($R73 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q73," & Chr(34) & Chr(34) & ")"
Range("$U74").Formula = "=IF(($S74 > 0)*($R74 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q74," & Chr(34) & Chr(34) & ")"
Range("$U75").Formula = "=IF(($S75 > 0)*($R75 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q75," & Chr(34) & Chr(34) & ")"
Range("$U76").Formula = "=IF(($S76 > 0)*($R76 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q76," & Chr(34) & Chr(34) & ")"
Range("$U77").Formula = "=IF(($S77 > 0)*($R77 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q77," & Chr(34) & Chr(34) & ")"
Range("$U78").Formula = "=IF(($S78 > 0)*($R78 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q78," & Chr(34) & Chr(34) & ")"
Range("$U79").Formula = "=IF(($S79 > 0)*($R79 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q79," & Chr(34) & Chr(34) & ")"

Range("$U80").Formula = "=IF(($S80 > 0)*($R80 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q80," & Chr(34) & Chr(34) & ")"
Range("$U81").Formula = "=IF(($S81 > 0)*($R81 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q81," & Chr(34) & Chr(34) & ")"
Range("$U82").Formula = "=IF(($S82 > 0)*($R82 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q82," & Chr(34) & Chr(34) & ")"
Range("$U83").Formula = "=IF(($S83 > 0)*($R83 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q83," & Chr(34) & Chr(34) & ")"
Range("$U84").Formula = "=IF(($S84 > 0)*($R84 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q84," & Chr(34) & Chr(34) & ")"
Range("$U85").Formula = "=IF(($S85 > 0)*($R85 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q85," & Chr(34) & Chr(34) & ")"
Range("$U86").Formula = "=IF(($S86 > 0)*($R86 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q86," & Chr(34) & Chr(34) & ")"
Range("$U87").Formula = "=IF(($S87 > 0)*($R87 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q87," & Chr(34) & Chr(34) & ")"
Range("$U88").Formula = "=IF(($S88 > 0)*($R88 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q88," & Chr(34) & Chr(34) & ")"
Range("$U89").Formula = "=IF(($S89 > 0)*($R89 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q89," & Chr(34) & Chr(34) & ")"

Range("$U90").Formula = "=IF(($S90 > 0)*($R90 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q90," & Chr(34) & Chr(34) & ")"
Range("$U91").Formula = "=IF(($S91 > 0)*($R91 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q91," & Chr(34) & Chr(34) & ")"
Range("$U92").Formula = "=IF(($S92 > 0)*($R92 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q92," & Chr(34) & Chr(34) & ")"
Range("$U93").Formula = "=IF(($S93 > 0)*($R93 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q93," & Chr(34) & Chr(34) & ")"
Range("$U94").Formula = "=IF(($S94 > 0)*($R94 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q94," & Chr(34) & Chr(34) & ")"
Range("$U95").Formula = "=IF(($S95 > 0)*($R95 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q95," & Chr(34) & Chr(34) & ")"
Range("$U96").Formula = "=IF(($S96 > 0)*($R96 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q96," & Chr(34) & Chr(34) & ")"
Range("$U97").Formula = "=IF(($S97 > 0)*($R97 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q97," & Chr(34) & Chr(34) & ")"
Range("$U98").Formula = "=IF(($S98 > 0)*($R98 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q98," & Chr(34) & Chr(34) & ")"
Range("$U99").Formula = "=IF(($S99 > 0)*($R99 > 0.00001), =ROUNDDOWN(($Y$17-$Y$18)/$Q99," & Chr(34) & Chr(34) & ")"

End Sub

There are two mistakes

 

1. There should not "=" inside IF-function

2. The function ROUNDDOWN need parameter and the ")" 

 

The following is an amended statement.
Range("$U30").Formula = "=IF(($S30 > 0)*($R30 > 0.00001), ROUNDDOWN(($Y$17-$Y$18)/$Q30,1)," & Chr(34) & Chr(34) & ")"

 

By the way, I saw you repeated the formulas a lot. I think you can try the for-loop to generate the formula:

 

For r = 30 To 99
       Range("$U" & r).Formula = "=IF(($S" & r & " > 0)*($R" & r & " > 0.00001),     

         ROUNDDOWN(($Y$17-$Y$18)/$Q" & r & ",1)," & Chr(34) & Chr(34) & ")"
Next r

Man Fai, thank you so much for your assistance. I feel like I'm getting real close to the end. However I cut paste your entire code and got Compile syntax error on the ROUNDDOWN line:

 

Sub Enter_Formulas()

For r = 30 To 99
Range("$U" & r).Formula = "=IF(($S" & r & " > 0)*($R" & r & " > 0.00001), "

ROUNDDOWN(($Y$17-$Y$18)/$Q" & r & ",1)," & Chr(34) & Chr(34) & ")"
Next r

End Sub

 

Nevermind my reply. I found that the macro was automatically inserting a "quote" symbol before the ROUNDDOWN. I removed it and the compile error when away. I am seeing one other issue however. It appears that the value that the formula injects into the "U" column may not be remaining. It could be due to the fact that the spreadsheet is not in calculation mode when I run these test. I will initiate calculation mode shortly and let you know the result.  

Wow, After initiating Calculation mode Everything worked almost perfect except "U" column values did not round down. What do you think is the cause? Does macro recognize the ROUNDDOWN 

Let me clarify. I did get proper values in column U, but the were fractional like 2.4 or 1.3. which fractional values can not be utilized by this algorithm in the end. 

best response confirmed by Greg Bonaparte (Iron Contributor)
Solution

ROUNDDOWN is a built-in function. The use of it is 

 =ROUNDDOWN(number, num_of_digit)

 

The macro you wrote is a procedure to repeatedly insert function/formula into the cell in column U. So, it is not related to whether macro recognize ROUNDDOWN

 

You may try to change the num_of_digit parameter to see if it works. I thought ROUNDDOWN(A1,0) will gives a value round down to the nearest integer. 

 


@Greg Bonaparte wrote:

Let me clarify. I did get proper values in column U, but the were fractional like 2.4 or 1.3. which fractional values can not be utilized by this algorithm in the end. 


It seems normal. The following are some examples of ROUNDDOWN.

ROUNDDOWN(2.123456, 1) = 2.1

ROUNDDOWN(2.123456, 2) = 2.12

ROUNDDOWN(2.123456, 3) = 2.123

1 best response

Accepted Solutions
best response confirmed by Greg Bonaparte (Iron Contributor)
Solution

ROUNDDOWN is a built-in function. The use of it is 

 =ROUNDDOWN(number, num_of_digit)

 

The macro you wrote is a procedure to repeatedly insert function/formula into the cell in column U. So, it is not related to whether macro recognize ROUNDDOWN

 

You may try to change the num_of_digit parameter to see if it works. I thought ROUNDDOWN(A1,0) will gives a value round down to the nearest integer. 

 

View solution in original post