Feb 18 2019 01:47 PM
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
Feb 18 2019 01:54 PM
Additional Info:
The error says:
Run-time error '1004':
Application-defined or object-defined error
Feb 18 2019 04:11 PM
Feb 18 2019 04:20 PM
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,"")"
Feb 18 2019 04:50 PM
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.
Feb 19 2019 12:43 AM - edited Feb 19 2019 12:43 AM
No I mean this sorry
Range("$AZ30").Formula = "=IF(($S30 > 0)*($R30 > 0), $U30=$S30,0)"
Feb 19 2019 01:19 AM
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.
Feb 19 2019 01:28 AM
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.
Feb 19 2019 01:52 AM
Thank you Man Fai Chan,
I tried both suggestions. Both formulas left a dashs "---" in place of the "false".
Feb 19 2019 03:36 AM
Feb 19 2019 12:24 PM
Feb 19 2019 01:44 PM
Feb 20 2019 09:48 PM
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
Feb 20 2019 10:39 PM
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
Feb 21 2019 01:59 AM
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
Feb 21 2019 02:09 AM
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.
Feb 21 2019 02:30 AM
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
Feb 21 2019 02:33 AM
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.
Feb 21 2019 02:35 AM
SolutionROUNDDOWN 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.
Feb 21 2019 02:37 AM
@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
Feb 21 2019 02:35 AM
SolutionROUNDDOWN 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.