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 21, 2019

        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.  

Resources