SOLVED
Home

Debug Error on Macro

%3CLINGO-SUB%20id%3D%22lingo-sub-352763%22%20slang%3D%22en-US%22%3EDebug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352763%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20any%20know%20why%20I%20get%20Debugged%20in%20the%20following%20macro.%20I%20copied%20the%20syntax%20directly%20from%20a%20video%20tutorial%20and%20have%20double%20checked%20that%20it%20appears%20correct.%26nbsp%3B%20The%20the%20first%20debug%20(yellow%20highlight)%20appears%20on%20the%201st%20line%20starting%20with%20%22Range...%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Enter_Formulas()%3C%2FP%3E%3CP%3ERange(%22%24AZ30%22).Formula%20%3D%20%22%3DIF((%24S30%20%26gt%3B%200)*(%24R30%20%26gt%3B%200)%2C%20%24U30%3D%24S30%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ31%22).Formula%20%3D%20%22%3DIF((%24S31%20%26gt%3B%200)*(%24R31%20%26gt%3B%200)%2C%20%24U31%3D%24S31%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ32%22).Formula%20%3D%20%22%3DIF((%24S32%20%26gt%3B%200)*(%24R32%20%26gt%3B%200)%2C%20%24U32%3D%24S32%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ33%22).Formula%20%3D%20%22%3DIF((%24S33%20%26gt%3B%200)*(%24R33%20%26gt%3B%200)%2C%20%24U33%3D%24S33%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ34%22).Formula%20%3D%20%22%3DIF((%24S34%20%26gt%3B%200)*(%24R34%20%26gt%3B%200)%2C%20%24U34%3D%24S34%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ35%22).Formula%20%3D%20%22%3DIF((%24S35%20%26gt%3B%200)*(%24R35%20%26gt%3B%200)%2C%20%24U35%3D%24S35%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ36%22).Formula%20%3D%20%22%3DIF((%24S36%20%26gt%3B%200)*(%24R36%20%26gt%3B%200)%2C%20%24U36%3D%24S36%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ37%22).Formula%20%3D%20%22%3DIF((%24S37%20%26gt%3B%200)*(%24R37%20%26gt%3B%200)%2C%20%24U37%3D%24S37%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ38%22).Formula%20%3D%20%22%3DIF((%24S38%20%26gt%3B%200)*(%24R38%20%26gt%3B%200)%2C%20%24U38%3D%24S38%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ39%22).Formula%20%3D%20%22%3DIF((%24S39%20%26gt%3B%200)*(%24R39%20%26gt%3B%200)%2C%20%24U39%3D%24S39%2C%22%22)%22%3C%2FP%3E%3CP%3ERange(%22%24AZ40%22).Formula%20%3D%20%22%3DIF((%24S40%20%26gt%3B%200)*(%24R40%20%26gt%3B%200)%2C%20%24U40%3D%24S40%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ41%22).Formula%20%3D%20%22%3DIF((%24S41%20%26gt%3B%200)*(%24R41%20%26gt%3B%200)%2C%20%24U41%3D%24S41%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ42%22).Formula%20%3D%20%22%3DIF((%24S42%20%26gt%3B%200)*(%24R42%20%26gt%3B%200)%2C%20%24U42%3D%24S42%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ43%22).Formula%20%3D%20%22%3DIF((%24S43%20%26gt%3B%200)*(%24R44%20%26gt%3B%200)%2C%20%24U43%3D%24S43%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ44%22).Formula%20%3D%20%22%3DIF((%24S44%20%26gt%3B%200)*(%24R44%20%26gt%3B%200)%2C%20%24U44%3D%24S44%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ45%22).Formula%20%3D%20%22%3DIF((%24S45%20%26gt%3B%200)*(%24R45%20%26gt%3B%200)%2C%20%24U45%3D%24S45%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ46%22).Formula%20%3D%20%22%3DIF((%24S46%20%26gt%3B%200)*(%24R46%20%26gt%3B%200)%2C%20%24U46%3D%24S46%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ47%22).Formula%20%3D%20%22%3DIF((%24S47%20%26gt%3B%200)*(%24R47%20%26gt%3B%200)%2C%20%24U47%3D%24S47%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ48%22).Formula%20%3D%20%22%3DIF((%24S48%20%26gt%3B%200)*(%24R48%20%26gt%3B%200)%2C%20%24U48%3D%24S48%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ49%22).Formula%20%3D%20%22%3DIF((%24S49%20%26gt%3B%200)*(%24R49%20%26gt%3B%200)%2C%20%24U49%3D%24S49%2C%22%22)%22%3C%2FP%3E%3CP%3ERange(%22%24AZ50%22).Formula%20%3D%20%22%3DIF((%24S50%20%26gt%3B%200)*(%24R50%20%26gt%3B%200)%2C%20%24U50%3D%24S50%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ51%22).Formula%20%3D%20%22%3DIF((%24S51%20%26gt%3B%200)*(%24R51%20%26gt%3B%200)%2C%20%24U51%3D%24S51%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ52%22).Formula%20%3D%20%22%3DIF((%24S52%20%26gt%3B%200)*(%24R52%20%26gt%3B%200)%2C%20%24U52%3D%24S52%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ53%22).Formula%20%3D%20%22%3DIF((%24S53%20%26gt%3B%200)*(%24R53%20%26gt%3B%200)%2C%20%24U53%3D%24S53%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ54%22).Formula%20%3D%20%22%3DIF((%24S54%20%26gt%3B%200)*(%24R54%20%26gt%3B%200)%2C%20%24U54%3D%24S54%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ55%22).Formula%20%3D%20%22%3DIF((%24S55%20%26gt%3B%200)*(%24R55%20%26gt%3B%200)%2C%20%24U55%3D%24S55%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ56%22).Formula%20%3D%20%22%3DIF((%24S56%20%26gt%3B%200)*(%24R56%20%26gt%3B%200)%2C%20%24U56%3D%24S56%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ57%22).Formula%20%3D%20%22%3DIF((%24S57%20%26gt%3B%200)*(%24R57%20%26gt%3B%200)%2C%20%24U57%3D%24S57%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ58%22).Formula%20%3D%20%22%3DIF((%24S58%20%26gt%3B%200)*(%24R58%20%26gt%3B%200)%2C%20%24U58%3D%24S58%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ59%22).Formula%20%3D%20%22%3DIF((%24S59%20%26gt%3B%200)*(%24R59%20%26gt%3B%200)%2C%20%24U59%3D%24S59%2C%22%22)%22%3C%2FP%3E%3CP%3ERange(%22%24AZ60%22).Formula%20%3D%20%22%3DIF((%24S60%20%26gt%3B%200)*(%24R60%20%26gt%3B%200)%2C%20%24U60%3D%24S60%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ61%22).Formula%20%3D%20%22%3DIF((%24S61%20%26gt%3B%200)*(%24R61%20%26gt%3B%200)%2C%20%24U61%3D%24S61%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ62%22).Formula%20%3D%20%22%3DIF((%24S62%20%26gt%3B%200)*(%24R62%20%26gt%3B%200)%2C%20%24U62%3D%24S62%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ63%22).Formula%20%3D%20%22%3DIF((%24S63%20%26gt%3B%200)*(%24R63%20%26gt%3B%200)%2C%20%24U63%3D%24S63%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ64%22).Formula%20%3D%20%22%3DIF((%24S64%20%26gt%3B%200)*(%24R64%20%26gt%3B%200)%2C%20%24U64%3D%24S64%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ65%22).Formula%20%3D%20%22%3DIF((%24S65%20%26gt%3B%200)*(%24R65%20%26gt%3B%200)%2C%20%24U65%3D%24S65%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ66%22).Formula%20%3D%20%22%3DIF((%24S66%20%26gt%3B%200)*(%24R66%20%26gt%3B%200)%2C%20%24U66%3D%24S66%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ67%22).Formula%20%3D%20%22%3DIF((%24S67%20%26gt%3B%200)*(%24R67%20%26gt%3B%200)%2C%20%24U67%3D%24S67%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ68%22).Formula%20%3D%20%22%3DIF((%24S68%20%26gt%3B%200)*(%24R68%20%26gt%3B%200)%2C%20%24U68%3D%24S68%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ69%22).Formula%20%3D%20%22%3DIF((%24S69%20%26gt%3B%200)*(%24R69%20%26gt%3B%200)%2C%20%24U69%3D%24S69%2C%22%22)%22%3C%2FP%3E%3CP%3ERange(%22%24AZ70%22).Formula%20%3D%20%22%3DIF((%24S70%20%26gt%3B%200)*(%24R70%20%26gt%3B%200)%2C%20%24U70%3D%24S70%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ71%22).Formula%20%3D%20%22%3DIF((%24S71%20%26gt%3B%200)*(%24R71%20%26gt%3B%200)%2C%20%24U71%3D%24S71%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ72%22).Formula%20%3D%20%22%3DIF((%24S72%20%26gt%3B%200)*(%24R72%20%26gt%3B%200)%2C%20%24U72%3D%24S72%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ73%22).Formula%20%3D%20%22%3DIF((%24S73%20%26gt%3B%200)*(%24R73%20%26gt%3B%200)%2C%20%24U73%3D%24S73%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ74%22).Formula%20%3D%20%22%3DIF((%24S74%20%26gt%3B%200)*(%24R74%20%26gt%3B%200)%2C%20%24U74%3D%24S74%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ75%22).Formula%20%3D%20%22%3DIF((%24S75%20%26gt%3B%200)*(%24R75%20%26gt%3B%200)%2C%20%24U75%3D%24S75%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ76%22).Formula%20%3D%20%22%3DIF((%24S76%20%26gt%3B%200)*(%24R76%20%26gt%3B%200)%2C%20%24U76%3D%24S76%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ77%22).Formula%20%3D%20%22%3DIF((%24S77%20%26gt%3B%200)*(%24R77%20%26gt%3B%200)%2C%20%24U77%3D%24S77%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ78%22).Formula%20%3D%20%22%3DIF((%24S78%20%26gt%3B%200)*(%24R78%20%26gt%3B%200)%2C%20%24U78%3D%24S78%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ79%22).Formula%20%3D%20%22%3DIF((%24S79%20%26gt%3B%200)*(%24R79%20%26gt%3B%200)%2C%20%24U79%3D%24S79%2C%22%22)%22%3C%2FP%3E%3CP%3ERange(%22%24AZ80%22).Formula%20%3D%20%22%3DIF((%24S80%20%26gt%3B%200)*(%24R80%20%26gt%3B%200)%2C%20%24U80%3D%24S80%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ81%22).Formula%20%3D%20%22%3DIF((%24S81%20%26gt%3B%200)*(%24R81%20%26gt%3B%200)%2C%20%24U81%3D%24S81%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ82%22).Formula%20%3D%20%22%3DIF((%24S82%20%26gt%3B%200)*(%24R82%20%26gt%3B%200)%2C%20%24U82%3D%24S82%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ83%22).Formula%20%3D%20%22%3DIF((%24S83%20%26gt%3B%200)*(%24R83%20%26gt%3B%200)%2C%20%24U83%3D%24S83%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ84%22).Formula%20%3D%20%22%3DIF((%24S84%20%26gt%3B%200)*(%24R84%20%26gt%3B%200)%2C%20%24U84%3D%24S84%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ85%22).Formula%20%3D%20%22%3DIF((%24S85%20%26gt%3B%200)*(%24R85%20%26gt%3B%200)%2C%20%24U85%3D%24S85%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ86%22).Formula%20%3D%20%22%3DIF((%24S86%20%26gt%3B%200)*(%24R86%20%26gt%3B%200)%2C%20%24U86%3D%24S86%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ87%22).Formula%20%3D%20%22%3DIF((%24S87%20%26gt%3B%200)*(%24R87%20%26gt%3B%200)%2C%20%24U87%3D%24S87%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ88%22).Formula%20%3D%20%22%3DIF((%24S88%20%26gt%3B%200)*(%24R88%20%26gt%3B%200)%2C%20%24U88%3D%24S88%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ89%22).Formula%20%3D%20%22%3DIF((%24S89%20%26gt%3B%200)*(%24R89%20%26gt%3B%200)%2C%20%24U89%3D%24S89%2C%22%22)%22%3C%2FP%3E%3CP%3ERange(%22%24AZ90%22).Formula%20%3D%20%22%3DIF((%24S90%20%26gt%3B%200)*(%24R90%20%26gt%3B%200)%2C%20%24U90%3D%24S90%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ91%22).Formula%20%3D%20%22%3DIF((%24S91%20%26gt%3B%200)*(%24R91%20%26gt%3B%200)%2C%20%24U91%3D%24S91%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ92%22).Formula%20%3D%20%22%3DIF((%24S92%20%26gt%3B%200)*(%24R92%20%26gt%3B%200)%2C%20%24U92%3D%24S92%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ93%22).Formula%20%3D%20%22%3DIF((%24S93%20%26gt%3B%200)*(%24R93%20%26gt%3B%200)%2C%20%24U93%3D%24S93%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ94%22).Formula%20%3D%20%22%3DIF((%24S94%20%26gt%3B%200)*(%24R94%20%26gt%3B%200)%2C%20%24U94%3D%24S94%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ95%22).Formula%20%3D%20%22%3DIF((%24S95%20%26gt%3B%200)*(%24R95%20%26gt%3B%200)%2C%20%24U95%3D%24S95%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ96%22).Formula%20%3D%20%22%3DIF((%24S96%20%26gt%3B%200)*(%24R96%20%26gt%3B%200)%2C%20%24U96%3D%24S96%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ97%22).Formula%20%3D%20%22%3DIF((%24S97%20%26gt%3B%200)*(%24R97%20%26gt%3B%200)%2C%20%24U97%3D%24S97%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ98%22).Formula%20%3D%20%22%3DIF((%24S98%20%26gt%3B%200)*(%24R98%20%26gt%3B%200)%2C%20%24U98%3D%24S98%2C%22%22)%22%3CBR%20%2F%3ERange(%22%24AZ99%22).Formula%20%3D%20%22%3DIF((%24S99%20%26gt%3B%200)*(%24R99%20%26gt%3B%200)%2C%20%24U99%3D%24S99%2C%22%22)%22%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-352763%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355872%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355872%22%20slang%3D%22en-US%22%3EPersonally%20%2C%20since%20the%20cells%20seem%20to%20be%20contiguous%20as%20a%20starter%20I%20would%20use%20a%20single%20expression%3A%3CBR%20%2F%3ERange(%22AZ30%3A%24AZ99%22).Formula%20%3D%20%5BHere%20use%20the%20first%20formula%20at%20the%20top%5D%3CBR%20%2F%3EThis%20expression%20will%20automatically%20copy%20the%20formula%20in%20each%20cell%20as%20relative%20formulas.%3CBR%20%2F%3EPlease%20look%20at%20the%20double%20quotation%20marks%20at%20the%20end.%20As%20in%20.....%20%24U99%3D%24S99%2C%22%22)%22%3CBR%20%2F%3EAre%20you%20sure%20they're%20ok%3F%3CBR%20%2F%3EWhile%20I'm%20looking%20at%20the%20formula%2C%20since%20they%20are%20all%20copied%20down%20in%20column%20AZ%2C%20you%20do%20not%20need%20to%20%24%20in%20the%20reference.%3CBR%20%2F%3EDaniel%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355860%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355860%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Man%20Fai%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETurns%20out%20I%20went%20back%20to%20ROUNDDOWN%3C%2FP%3E%3CP%3ESub%20Enter_Formulas()%3C%2FP%3E%3CP%3EFor%20r%20%3D%2030%20To%2033%3CBR%20%2F%3ERange(%22%24U%22%20%26amp%3B%20r).Formula%20%3D%20%22%3DIF((%24S%22%20%26amp%3B%20r%20%26amp%3B%20%22%20%26gt%3B%200)*(%24R%22%20%26amp%3B%20r%20%26amp%3B%20%22%20%26gt%3B%200.00001)%2C%20ROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q%22%20%26amp%3B%20r%20%26amp%3B%20%22%2C0)%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ENext%20r%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAbove%20macro%20works%20but%20I%20get%20conflict%20from%26nbsp%3B%20cell%20Y18%26nbsp%3B%20indirectly%20conflicting%20with%20S30%2C%20U30%20%26amp%3B%20V30.%20I'm%20wondering%20if%20there%20is%20a%20way%20to%20tell%20the%20macro%20to%20disregard%20the%20conflict.%20A%20value%20in%20the%20%22NEXT%20r%22%20will%20be%20modified%20when%20%22this%20r%22%20is%20processed%20which%20I%20believe%20is%20the%20indirect%20conflict.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355846%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355846%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EI%20was%20able%20t%20fix%20the%20syntax%20with%20the%20following%3A%20But%20troubles%20remain.%20Cells%20are%20conflicting%20within%20the%20spreadsheet.%20So%20my%20issue%20is%20not%20with%20the%20macro.%20Ill%20need%20to%20investigate%20the%20issues%20outside%20of%20macro.%20Thanks%20again%20for%20you%20help.%3C%2FP%3E%3CP%3ESub%20Enter_Formulas()%3C%2FP%3E%3CP%3EFor%20r%20%3D%2030%20To%2037%3CBR%20%2F%3ERange(%22%24U%22%20%26amp%3B%20r).Formula%20%3D%20%22%3DSUM(%24Y%2417-%24Y%2418)%22%3C%2FP%3E%3CP%3ENext%20r%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EI%20wonder%20why%20you%20use%20SUM-function%20but%20have%20the%20minus%20sign%20included.%20Moreover%2C%20if%20you%20want%20to%20sum%20two%20cells%2C%20it%20would%20be%20better%20to%20use%20%22%3DY17%2BY18%22.%20If%20you%20want%20to%20sum%20more%20cells%2C%20SUM-function%20is%20useful%20(E.g.%20SUM(Y17%3AY28)).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355807%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355807%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20able%20t%20fix%20the%20syntax%20with%20the%20following%3A%20But%20troubles%20remain.%20Cells%20are%20conflicting%20within%20the%20spreadsheet.%20So%20my%20issue%20is%20not%20with%20the%20macro.%20Ill%20need%20to%20investigate%20the%20issues%20outside%20of%20macro.%20Thanks%20again%20for%20you%20help.%3C%2FP%3E%3CP%3ESub%20Enter_Formulas()%3C%2FP%3E%3CP%3EFor%20r%20%3D%2030%20To%2037%3CBR%20%2F%3ERange(%22%24U%22%20%26amp%3B%20r).Formula%20%3D%20%22%3DSUM(%24Y%2417-%24Y%2418)%22%3C%2FP%3E%3CP%3ENext%20r%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355802%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355802%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20so%20much%20for%20your%20assistance%20Man%20Fai.%26nbsp%3B%20With%20your%20help%20I%20was%20able%20to%20come%20really%20close%20to%20the%20end%20of%20a%204%20year%20project.%20I%20have%20removed%20ROUNDDOWN%20from%20the%20macro%20and%20moved%20it%20to%20within%20cells.%20I%20believe%20I%20can%20simplify%20the%20process%20by%20using%20SUM%20instead.%20However%20I%20get%20a%20syntax%20error.%20See%20macro%20below%3A%3C%2FP%3E%3CP%3ESub%20Enter_Formulas()%3C%2FP%3E%3CP%3EFor%20r%20%3D%2030%20To%2037%3CBR%20%2F%3ESUM(%24Y%2417-%24Y%2418)%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ENext%20r%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354683%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354683%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20see%20the%20attachment%20for%20the%20syntax%20of%20ROUNDDOWN%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354645%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354645%22%20slang%3D%22en-US%22%3E%3CP%3EHmm%2C%20Can%20you%20write%20the%20formula%20that%20rounds%20down%20to%20zero%20so%20I%20can%20see%20the%20full%20syntax.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354609%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354609%22%20slang%3D%22en-US%22%3E%3CP%3EFollow%20up%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20see%20that%20somehow%20in%20the%20conversation%20my%20original%20roundown%20formula%20was%20changed.%20This%20is%20what%20it%20is%20outside%20of%20the%20macro%3A%3C%2FP%3E%3CP%3E%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q30%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354605%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354605%22%20slang%3D%22en-US%22%3E%3CP%3EProblem%20found%20but%20not%20resolved.%20In%20the%20original%20formula%20q30%20is%20round%20down%20to%20the%20nearest%20zero.%20In%20your%20formula%20q30%2C0%20become%20q%20r%20without%20zero.%20Can%20you%20rephrase%20syntax%20to%20include%20zero%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354604%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354604%22%20slang%3D%22en-US%22%3E%3CP%3EProblem%20found%20but%20not%20resolved.%20In%20the%20original%20formula%20q30%20is%20round%20down%20to%20the%20nearest%20zero.%20I%20your%20formula%20q30%2C0%20become%20q%20r%20without%20zero.%20Can%20you%20rephrase%20syntax%20to%20include%20zero%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354595%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354595%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3ELet%20me%20clarify.%20I%20did%20get%20proper%20values%20in%20column%20U%2C%20but%20the%20were%20fractional%20like%202.4%20or%201.3.%20which%20fractional%20values%20can%20not%20be%20utilized%20by%20this%20algorithm%20in%20the%20end.%26nbsp%3B%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EIt%20seems%20normal.%20The%20following%20are%20some%20examples%20of%20ROUNDDOWN.%3C%2FP%3E%3CP%3EROUNDDOWN(2.123456%2C%201)%20%3D%202.1%3C%2FP%3E%3CP%3EROUNDDOWN(2.123456%2C%202)%20%3D%202.12%3C%2FP%3E%3CP%3EROUNDDOWN(2.123456%2C%203)%20%3D%202.123%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354593%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354593%22%20slang%3D%22en-US%22%3E%3CP%3EROUNDDOWN%20is%20a%20built-in%20function.%20The%20use%20of%20it%20is%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3DROUNDDOWN(number%2C%20num_of_digit)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20macro%20you%20wrote%20is%20a%20procedure%20to%20repeatedly%20insert%20function%2Fformula%20into%20the%20cell%20in%20column%20U.%20So%2C%20it%20is%20not%20related%20to%20whether%20macro%20recognize%20ROUNDDOWN%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20to%20change%20the%20num_of_digit%20parameter%20to%20see%20if%20it%20works.%20I%20thought%20ROUNDDOWN(A1%2C0)%20will%20gives%20a%20value%20round%20down%20to%20the%20nearest%20integer.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354591%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354591%22%20slang%3D%22en-US%22%3E%3CP%3ELet%20me%20clarify.%20I%20did%20get%20proper%20values%20in%20column%20U%2C%20but%20the%20were%20fractional%20like%202.4%20or%201.3.%20which%20fractional%20values%20can%20not%20be%20utilized%20by%20this%20algorithm%20in%20the%20end.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354589%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354589%22%20slang%3D%22en-US%22%3E%3CP%3EWow%2C%20After%20initiating%20Calculation%20mode%20Everything%20worked%20almost%20perfect%20except%20%22U%22%20column%20values%20did%20not%20round%20down.%20What%20do%20you%20think%20is%20the%20cause%3F%20Does%20macro%20recognize%20the%20ROUNDDOWN%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354580%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354580%22%20slang%3D%22en-US%22%3E%3CP%3ENevermind%20my%20reply.%20I%20found%20that%20the%20macro%20was%20automatically%20inserting%20a%20%22quote%22%20symbol%20before%20the%20ROUNDDOWN.%20I%20removed%20it%20and%20the%20compile%20error%20when%20away.%20I%20am%20seeing%20one%20other%20issue%20however.%20It%20appears%20that%20the%20value%20that%20the%20formula%20injects%20into%20the%20%22U%22%20column%20may%20not%20be%20remaining.%20It%20could%20be%20due%20to%20the%20fact%20that%20the%20spreadsheet%20is%20not%20in%20calculation%20mode%20when%20I%20run%20these%20test.%20I%20will%20initiate%20calculation%20mode%20shortly%20and%20let%20you%20know%20the%20result.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354578%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354578%22%20slang%3D%22en-US%22%3E%3CP%3EMan%20Fai%2C%20thank%20you%20so%20much%20for%20your%20assistance.%20I%20feel%20like%20I'm%20getting%20real%20close%20to%20the%20end.%20However%20I%20cut%20paste%20your%20entire%20code%20and%20got%20Compile%20syntax%20error%20on%20the%20ROUNDDOWN%20line%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Enter_Formulas()%3C%2FP%3E%3CP%3EFor%20r%20%3D%2030%20To%2099%3CBR%20%2F%3ERange(%22%24U%22%20%26amp%3B%20r).Formula%20%3D%20%22%3DIF((%24S%22%20%26amp%3B%20r%20%26amp%3B%20%22%20%26gt%3B%200)*(%24R%22%20%26amp%3B%20r%20%26amp%3B%20%22%20%26gt%3B%200.00001)%2C%20%22%3C%2FP%3E%3CP%3EROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q%22%20%26amp%3B%20r%20%26amp%3B%20%22%2C1)%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ENext%20r%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354498%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354498%22%20slang%3D%22en-US%22%3E%3CP%3EThere%20are%20two%20mistakes%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20There%20should%20not%20%22%3D%22%20inside%20IF-function%3C%2FP%3E%3CP%3E2.%20The%20function%20ROUNDDOWN%20need%20parameter%20and%20the%20%22)%22%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20is%20an%20amended%20statement.%3CBR%20%2F%3ERange(%22%24U30%22).Formula%20%3D%20%22%3DIF((%24S30%20%26gt%3B%200)*(%24R30%20%26gt%3B%200.00001)%2C%20ROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q30%2C1)%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20I%20saw%20you%20repeated%20the%20formulas%20a%20lot.%20I%20think%20you%20can%20try%20the%20for-loop%20to%20generate%20the%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20r%20%3D%2030%20To%2099%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BRange(%22%24U%22%20%26amp%3B%20r).Formula%20%3D%20%22%3DIF((%24S%22%20%26amp%3B%20r%20%26amp%3B%20%22%20%26gt%3B%200)*(%24R%22%20%26amp%3B%20r%20%26amp%3B%20%22%20%26gt%3B%200.00001)%2C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q%22%20%26amp%3B%20r%20%26amp%3B%20%22%2C1)%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ENext%20r%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-354397%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-354397%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20updated%20the%20formula%20to%20reflect%20the%20ultimate%20goal.%20I've%20tried%20all%20suggestions%20above.%20I%20get%20debug%20error%20with%20each.%20See%20final%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Enter_Formulas()%3C%2FP%3E%3CP%3ERange(%22%24U30%22).Formula%20%3D%20%22%3DIF((%24S30%20%26gt%3B%200)*(%24R30%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q30%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U31%22).Formula%20%3D%20%22%3DIF((%24S31%20%26gt%3B%200)*(%24R31%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q31%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U32%22).Formula%20%3D%20%22%3DIF((%24S32%20%26gt%3B%200)*(%24R32%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q32%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U33%22).Formula%20%3D%20%22%3DIF((%24S33%20%26gt%3B%200)*(%24R33%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q33%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U34%22).Formula%20%3D%20%22%3DIF((%24S34%20%26gt%3B%200)*(%24R34%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q34%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U35%22).Formula%20%3D%20%22%3DIF((%24S35%20%26gt%3B%200)*(%24R35%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q35%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U36%22).Formula%20%3D%20%22%3DIF((%24S36%20%26gt%3B%200)*(%24R36%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q36%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U37%22).Formula%20%3D%20%22%3DIF((%24S37%20%26gt%3B%200)*(%24R37%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q37%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U38%22).Formula%20%3D%20%22%3DIF((%24S38%20%26gt%3B%200)*(%24R38%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q38%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U39%22).Formula%20%3D%20%22%3DIF((%24S39%20%26gt%3B%200)*(%24R39%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q39%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3C%2FP%3E%3CP%3ERange(%22%24U40%22).Formula%20%3D%20%22%3DIF((%24S40%20%26gt%3B%200)*(%24R40%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q40%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U41%22).Formula%20%3D%20%22%3DIF((%24S41%20%26gt%3B%200)*(%24R41%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q41%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U42%22).Formula%20%3D%20%22%3DIF((%24S42%20%26gt%3B%200)*(%24R42%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q42%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U43%22).Formula%20%3D%20%22%3DIF((%24S43%20%26gt%3B%200)*(%24R44%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q43%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U44%22).Formula%20%3D%20%22%3DIF((%24S44%20%26gt%3B%200)*(%24R44%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q44%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U45%22).Formula%20%3D%20%22%3DIF((%24S45%20%26gt%3B%200)*(%24R45%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q45%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U46%22).Formula%20%3D%20%22%3DIF((%24S46%20%26gt%3B%200)*(%24R46%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q46%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U47%22).Formula%20%3D%20%22%3DIF((%24S47%20%26gt%3B%200)*(%24R47%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q47%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U48%22).Formula%20%3D%20%22%3DIF((%24S48%20%26gt%3B%200)*(%24R48%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q48%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U49%22).Formula%20%3D%20%22%3DIF((%24S49%20%26gt%3B%200)*(%24R49%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q49%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3C%2FP%3E%3CP%3ERange(%22%24U50%22).Formula%20%3D%20%22%3DIF((%24S50%20%26gt%3B%200)*(%24R50%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q50%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U51%22).Formula%20%3D%20%22%3DIF((%24S51%20%26gt%3B%200)*(%24R51%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q51%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U52%22).Formula%20%3D%20%22%3DIF((%24S52%20%26gt%3B%200)*(%24R52%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q52%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U53%22).Formula%20%3D%20%22%3DIF((%24S53%20%26gt%3B%200)*(%24R53%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q53%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U54%22).Formula%20%3D%20%22%3DIF((%24S54%20%26gt%3B%200)*(%24R54%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q55%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U55%22).Formula%20%3D%20%22%3DIF((%24S55%20%26gt%3B%200)*(%24R55%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q56%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U56%22).Formula%20%3D%20%22%3DIF((%24S56%20%26gt%3B%200)*(%24R56%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q57%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U57%22).Formula%20%3D%20%22%3DIF((%24S57%20%26gt%3B%200)*(%24R57%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q58%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U58%22).Formula%20%3D%20%22%3DIF((%24S58%20%26gt%3B%200)*(%24R58%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q59%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U59%22).Formula%20%3D%20%22%3DIF((%24S59%20%26gt%3B%200)*(%24R59%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q60%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3C%2FP%3E%3CP%3ERange(%22%24U60%22).Formula%20%3D%20%22%3DIF((%24S60%20%26gt%3B%200)*(%24R60%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q60%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U61%22).Formula%20%3D%20%22%3DIF((%24S61%20%26gt%3B%200)*(%24R61%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q61%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U62%22).Formula%20%3D%20%22%3DIF((%24S62%20%26gt%3B%200)*(%24R62%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q62%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U63%22).Formula%20%3D%20%22%3DIF((%24S63%20%26gt%3B%200)*(%24R63%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q63%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U64%22).Formula%20%3D%20%22%3DIF((%24S64%20%26gt%3B%200)*(%24R64%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q64%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U65%22).Formula%20%3D%20%22%3DIF((%24S65%20%26gt%3B%200)*(%24R65%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q65%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U66%22).Formula%20%3D%20%22%3DIF((%24S66%20%26gt%3B%200)*(%24R66%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q66%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U67%22).Formula%20%3D%20%22%3DIF((%24S67%20%26gt%3B%200)*(%24R67%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q67%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U68%22).Formula%20%3D%20%22%3DIF((%24S68%20%26gt%3B%200)*(%24R68%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q68%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U69%22).Formula%20%3D%20%22%3DIF((%24S69%20%26gt%3B%200)*(%24R69%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q69%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3C%2FP%3E%3CP%3ERange(%22%24U70%22).Formula%20%3D%20%22%3DIF((%24S70%20%26gt%3B%200)*(%24R70%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q70%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U71%22).Formula%20%3D%20%22%3DIF((%24S71%20%26gt%3B%200)*(%24R71%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q71%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U72%22).Formula%20%3D%20%22%3DIF((%24S72%20%26gt%3B%200)*(%24R72%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q72%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U73%22).Formula%20%3D%20%22%3DIF((%24S73%20%26gt%3B%200)*(%24R73%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q73%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U74%22).Formula%20%3D%20%22%3DIF((%24S74%20%26gt%3B%200)*(%24R74%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q74%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U75%22).Formula%20%3D%20%22%3DIF((%24S75%20%26gt%3B%200)*(%24R75%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q75%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U76%22).Formula%20%3D%20%22%3DIF((%24S76%20%26gt%3B%200)*(%24R76%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q76%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U77%22).Formula%20%3D%20%22%3DIF((%24S77%20%26gt%3B%200)*(%24R77%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q77%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U78%22).Formula%20%3D%20%22%3DIF((%24S78%20%26gt%3B%200)*(%24R78%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q78%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U79%22).Formula%20%3D%20%22%3DIF((%24S79%20%26gt%3B%200)*(%24R79%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q79%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3C%2FP%3E%3CP%3ERange(%22%24U80%22).Formula%20%3D%20%22%3DIF((%24S80%20%26gt%3B%200)*(%24R80%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q80%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U81%22).Formula%20%3D%20%22%3DIF((%24S81%20%26gt%3B%200)*(%24R81%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q81%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U82%22).Formula%20%3D%20%22%3DIF((%24S82%20%26gt%3B%200)*(%24R82%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q82%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U83%22).Formula%20%3D%20%22%3DIF((%24S83%20%26gt%3B%200)*(%24R83%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q83%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U84%22).Formula%20%3D%20%22%3DIF((%24S84%20%26gt%3B%200)*(%24R84%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q84%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U85%22).Formula%20%3D%20%22%3DIF((%24S85%20%26gt%3B%200)*(%24R85%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q85%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U86%22).Formula%20%3D%20%22%3DIF((%24S86%20%26gt%3B%200)*(%24R86%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q86%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U87%22).Formula%20%3D%20%22%3DIF((%24S87%20%26gt%3B%200)*(%24R87%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q87%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U88%22).Formula%20%3D%20%22%3DIF((%24S88%20%26gt%3B%200)*(%24R88%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q88%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U89%22).Formula%20%3D%20%22%3DIF((%24S89%20%26gt%3B%200)*(%24R89%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q89%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3C%2FP%3E%3CP%3ERange(%22%24U90%22).Formula%20%3D%20%22%3DIF((%24S90%20%26gt%3B%200)*(%24R90%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q90%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U91%22).Formula%20%3D%20%22%3DIF((%24S91%20%26gt%3B%200)*(%24R91%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q91%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U92%22).Formula%20%3D%20%22%3DIF((%24S92%20%26gt%3B%200)*(%24R92%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q92%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U93%22).Formula%20%3D%20%22%3DIF((%24S93%20%26gt%3B%200)*(%24R93%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q93%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U94%22).Formula%20%3D%20%22%3DIF((%24S94%20%26gt%3B%200)*(%24R94%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q94%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U95%22).Formula%20%3D%20%22%3DIF((%24S95%20%26gt%3B%200)*(%24R95%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q95%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U96%22).Formula%20%3D%20%22%3DIF((%24S96%20%26gt%3B%200)*(%24R96%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q96%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U97%22).Formula%20%3D%20%22%3DIF((%24S97%20%26gt%3B%200)*(%24R97%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q97%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U98%22).Formula%20%3D%20%22%3DIF((%24S98%20%26gt%3B%200)*(%24R98%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q98%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3CBR%20%2F%3ERange(%22%24U99%22).Formula%20%3D%20%22%3DIF((%24S99%20%26gt%3B%200)*(%24R99%20%26gt%3B%200.00001)%2C%20%3DROUNDDOWN((%24Y%2417-%24Y%2418)%2F%24Q99%2C%22%20%26amp%3B%20Chr(34)%20%26amp%3B%20Chr(34)%20%26amp%3B%20%22)%22%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353607%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353607%22%20slang%3D%22en-US%22%3EHi%20Greg%3CBR%20%2F%3E%3CBR%20%2F%3EA%20formula%20cannot%20put%20a%20value%20in%20a%20different%20cell.%3CBR%20%2F%3E%3CBR%20%2F%3ESounds%20a%20bit%20like%20you%20need%20the%20macro%20to%20run%20down%20the%20list%20of%20values%20and%20perform%20some%20actions%20based%20on%20tests.%20Unfortunately%20your%20formula%20based%20approach%20won%E2%80%99t%20work%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20can%20attach%20a%20sample%20file%20with%20some%20instructions%20on%20what%20you%20are%20wanting%20to%20do%20I%20can%20see%20if%20I%20can%20provide%20you%20with%20the%20appropriate%20code%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353565%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353565%22%20slang%3D%22en-US%22%3EHi%20Wyn%20thanks%20for%20your%20reply.%20In%20this%20formula%20I%20am%20trying%20to%20copy%20whatever%20value%20is%20found%20in%20S30%2C%20copy%20to%20U30%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353013%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353013%22%20slang%3D%22en-US%22%3ESo%20your%20formula%20checks%20if%20%24U30%3D%24S30%2C%20if%20they%20don't%20match%20it%20will%20therefore%20return%20FALSE%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you're%20hoping%20for%20something%20different%20maybe%20post%20a%20sample%20file%20with%20an%20explanation%20of%20what%20you%20need%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352960%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352960%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53148%22%20target%3D%22_blank%22%3EMan%20Fai%20Chan%2C%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20tried%20both%20suggestions.%20Both%20formulas%20left%20a%20dashs%20%22---%22%20in%20place%20of%20the%20%22false%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352953%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352953%22%20slang%3D%22en-US%22%3E%3CP%3EI%20think%20you%20want%20to%20type%20a%20formula%20in%20excel%20like%20the%20following%3A%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3D%20IF(Criteria%2C1%2C%22%22)%3C%2FP%3E%3CP%3EThe%20cell%20will%20be%20blank%20when%20the%20criteria%20is%20not%20true.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20the%20use%20of%20%22%22%20in%20VBA%20in%20a%20string%20will%20be%20viewed%20as%20one%20quotation%20(in%20order%20to%20distinguish%20with%20the%20string%20end).%20So%20you%20need%20to%20change%20the%20VBA%20statement%20as%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CSPAN%3ERange(%22%24AZ30%22).Formula%20%3D%20%22%3DIF((%24S30%20%26gt%3B%200)*(%24R30%20%26gt%3B%200)%2C%20%24U30%3D%24S30%2C%22%22%22%22)%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EYou%20may%20also%20try%20chr(34)%20for%20the%20use%20of%20quotation%20mark%20in%20.formula.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Range(%22%24AZ30%22).formula%20%3D%20%22%3DIF((%24S30%20%26gt%3B%200)*(%24R30%20%26gt%3B%200)%2C%20%24U30%20%3D%20%24S30%2C%22%20%26amp%3B%20chr(34)%20%26amp%3B%20chr(34)%20%26amp%3B%20%22)%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHope%20that%20it%20is%20helpful.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352944%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352944%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Wyn%2C%20The%20debug%20error%20went%20away.%20However%20instead%20of%20getting%20the%20value%20in%20u30%20to%20equal%20the%20value%20is%20s30%2C%20I%20get%20a%20%22false%22%20message%20in%20az30.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352931%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352931%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENo%20I%20mean%20this%20sorry%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ERange(%22%24AZ30%22).Formula%20%3D%20%22%3DIF((%24S30%20%26gt%3B%200)*(%24R30%20%26gt%3B%200)%2C%20%24U30%3D%24S30%2C0)%22%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352808%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352808%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20I%20attempted%20to%20do%20this%3C%2FP%3E%3CP%3ERange(%22%24AZ30%22).Formula%20%3D%20%22%3DIF((%24S30%20%26gt%3B%200)*(%24R30%20%26gt%3B%200)%2C%20%24U30%3D%24S30%2C%22%22)0%3C%2FP%3E%3CP%3Eevery%20line%20self%20populated%20with%20this%3C%2FP%3E%3CP%3ERange(%22%24AZ99%22).Formula%20%3D%20%22%3DIF((%24S99%20%26gt%3B%200)*(%24R99%20%26gt%3B%200)%2C%20%24U99%3D%24S99%2C%22%22)0%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eadding%20the%20quote%20sign%20automatically.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352802%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352802%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20your%20reply%20Wyn.%3C%2FP%3E%3CP%3EJust%20to%20clarify%2C%20Are%20you%20saying%20every%20line%20should%20look%20like%20this%3A%3C%2FP%3E%3CP%3ERange(%22%24AZ30%22).Formula%20%3D%20%22%3DIF((%24S30%20%26gt%3B%200)*(%24R30%20%26gt%3B%200)%2C%20%24U30%3D%24S30%2C%22%22)0%3C%2FP%3E%3CP%3Ewith%20the%20last%20line%20looking%20like%20this%3A%3C%2FP%3E%3CP%3ERange(%22%24AZ99%22).Formula%20%3D%20%22%3DIF((%24S99%20%26gt%3B%200)*(%24R99%20%26gt%3B%200)%2C%20%24U99%3D%24S99%2C%22%22)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352797%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352797%22%20slang%3D%22en-US%22%3EIt's%20due%20to%20the%20use%20of%20%22%22%20in%20your%20formula%20which%20the%20VBA%20code%20sees%20as%20the%20end%20of%20the%20string.%3CBR%20%2F%3E%3CBR%20%2F%3EEasiest%20fix%20is%20to%20replace%20%22%22%20with%200%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352764%22%20slang%3D%22en-US%22%3ERe%3A%20Debug%20Error%20on%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352764%22%20slang%3D%22en-US%22%3E%3CP%3EAdditional%20Info%3A%3C%2FP%3E%3CP%3EThe%20error%20says%3A%3C%2FP%3E%3CP%3ERun-time%20error%20'1004'%3A%3C%2FP%3E%3CP%3EApplication-defined%20or%20object-defined%20error%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Greg Bonaparte
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. 

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

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?

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?

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)

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

Please see the attachment for the syntax of ROUNDDOWN

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

 

 

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

 


@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)).

 

 

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.

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
Related Conversations
SharePoint online, list
JankaJMT in SharePoint on
1 Replies
Windows Server Checked/Debug Build
Christoffer A in Windows Server Insiders on
1 Replies