Forum Discussion

Greg Bonaparte's avatar
Greg Bonaparte
Iron Contributor
Feb 18, 2019
Solved

Debug Error on Macro

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) ap...
Macros and VBA
  • Man Fai Chan's avatar
    Man Fai Chan
    Feb 21, 2019

    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. 

     

Man Fai Chan's avatar
Man Fai Chan
Iron Contributor
Feb 19, 2019

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.

  • Greg Bonaparte's avatar
    Greg Bonaparte
    Iron Contributor
    Feb 21, 2019

    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

    • Man Fai Chan's avatar
      Man Fai Chan
      Iron Contributor
      Feb 21, 2019

      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

      • Greg Bonaparte's avatar
        Greg Bonaparte
        Iron Contributor
        Feb 21, 2019

        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

         

  • Greg Bonaparte's avatar
    Greg Bonaparte
    Iron Contributor
    Feb 19, 2019

    Thank you Man Fai Chan,

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

     

Resources