SOLVED

Gradient Tool

%3CLINGO-SUB%20id%3D%22lingo-sub-1411507%22%20slang%3D%22en-US%22%3EGradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1411507%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20I%20am%20using%20Excel%20for%20this%20and%20a%20custom%20function%2C%20my%20problem%20is%20the%20functions%20between%20I8%20and%20AV9%20altought%20some%20of%20them%20appear%20to%20work%2C%20sometimes%20they%20are%20giving%20me%20the%20Value%20Error.%20I%20think%20this%20is%20happening%20because%20when%20the%20functions%20between%20A7%20and%20AV7%20provide%20the%20RGB%20codes%20they%20provide%20255%208%20255%20instead%20of%20255%20008%20255%20and%20I%20need%20to%20add%20functions%20to%20the%20function%20to%20add%20the%200's.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1411507%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1411629%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1411629%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676901%22%20target%3D%22_blank%22%3E%40IsaacPiscopo%3C%2FA%3E%26nbsp%3B%20yes%20sort%20of.%26nbsp%3B%20Your%20formula%20for%20grabbing%20the%20RIGHT%20x%20characters%20is%20looking%20at%20how%20many%20characters%20are%20in%20the%201st%20section%20of%20the%20RGB%20code.%26nbsp%3B%20So%20you%20had%203%20characters%20for%20red%20so%20it%20was%20taking%20the%20last%203%20characters%20instead%20of%20only%201.%26nbsp%3B%20Here%20is%20an%20updated%20formula%3A%3C%2FP%3E%3CP%3E%3CFONT%3E%3DIF(%24E%2411%26gt%3B%3DI6-2%2C%22%23%22%26amp%3BDEC2HEX((LEFT(I7%2CSEARCH(%22%20%22%2CI7%2C1)-1))%2C2)%26amp%3BDEC2HEX((MID(I7%2CSEARCH(%22%20%22%2CI7)%2B1%2CSEARCH(%22%20%22%2CI7%2CSEARCH(%22%20%22%2CI7)%2B1)-SEARCH(%22%20%22%2CI7)-1))%2C2)%26amp%3BDEC2HEX((RIGHT(I7%2C%3CFONT%20color%3D%22%23ff0000%22%3ELEN(I7)-SEARCH(%22%20%22%2CI7%2CSEARCH(%22%20%22%2CI7)%2B1)%3C%2FFONT%3E))%2C2)%2C(%22%22))%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EI%20also%20updated%20the%20initial%20condition%20to%20%3CFONT%20color%3D%22%23ff0000%22%3E%24%3C%2FFONT%3EE%3CFONT%20color%3D%22%23ff0000%22%3E%24%3C%2FFONT%3E11%20and%20compared%20it%20to%20the%20numbers%20above%20it%20I6-2%20so%20it%20would%20be%20easy%20to%20copy%2Ffill%20and%20have%20excell%20automatically%20update%20all%20the%20cells.%26nbsp%3B%20That%20said%2C%20you%20MERGED%20all%20those%20cells%20which%20disables%20may%20useful%20things%20in%20excel.%26nbsp%3B%20If%20you%20want%20wider%20cells%20I%20recommend%20just%20highlighting%20those%20columns%20and%20dragging%20them%20wider%20or%20right%20click%20and%20select%20'column%20width'%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EI%20attached%20an%20updated%20file%20but%20never%20enabled%20the%20macros%20so%20they%20may%20or%20may%20not%20be%20attached.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1411882%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1411882%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676901%22%20target%3D%22_blank%22%3E%40IsaacPiscopo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20add%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E's%20post%2C%20if%20you%20have%20Office%20365%20then%20the%20formula%20in%20I8%20can%20be%20abbreviated%20to%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E%3DIF(%24E%2411%26gt%3B%3DI6-2%2C%22%23%22%26amp%3BCONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7%2C%22%20%22%2CREPT(%22%20%22%2C25))%2C%7B1%2C26%2C51%7D%2C25))%2C2))%2C%22%22)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412073%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412073%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3B%20Even%20without%20the%20new%20array%20functionality%20you%20might%20be%20able%20to%20use%20that%20as%20an%20array%20function%20or%20at%20least%20use%20that%20trick%20to%20%22simplify%22%20the%20existing%20formula%20to%20avoid%20the%20search%20of%20search%20-%20search%20...%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(%24E%2411%26gt%3B%3DI6-2%2C%22%23%22%26amp%3BCONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7%2C%22%20%22%2CREPT(%22%20%22%2C25))%2C1%2C25))%3CSPAN%3E%26amp%3BCONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7%2C%22%20%22%2CREPT(%22%20%22%2C25))%2C26%2C25))%3C%2FSPAN%3E%3CSPAN%3E%26amp%3BCONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7%2C%22%20%22%2CREPT(%22%20%22%2C25))%2C51%2C25))%3C%2FSPAN%3E%2C2))%2C%22%22)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412113%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412113%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActually%20my%20suggestion%20had%20nothing%20to%20do%20with%20the%20new%20array%20functionality%20within%20365.%20It%20works%20as%20a%20standalone%2C%20single-cell%20formula.%20My%20reference%20to%20365%20was%20based%20solely%20on%20the%20use%20of%20the%20CONCAT%20function%2C%20which%20I%20(perhaps%20erroneously%3F)%20believed%20was%20only%20available%20to%20365%20subscribers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%20Why%20are%20you%20using%20CONCAT%20in%20the%20construction%20you%20posted%3F%20Seems%20superfluous%20to%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412175%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412175%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3B%20you%20are%20correct.%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20I%20didn't%20recognize%20any%20of%20those%20functions%20as%20inherently%20being%20array%20functions%20that%20would%20work%20with%20that%20array%20input%20%7B1%2C%2026%2C%2051%7D%20but%20apparently%20it%20works%20fine%20(at%20least%20in%20my%20excel)%3C%2FP%3E%3CP%3E2)%20concat%20is%20wasteful%20because%20I%20was%20lazy%20and%20quick%20to%20copy%20and%20paste%20your%20formula%203%20times%20to%20avoid%20the%20%7B%E2%80%A6%7D%20array%20portion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412710%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412710%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20changed%20all%20the%20boxes%20and%20yeah%20now%20the%20RGB%20box%20is%20broken.%20I%20also%20changed%20the%20*%20part%20to%20cell%20boxes%20so%20its%20better%20for%20autofill.(btw%20I'm%20using%20Microsoft%20Office%20365%2C%20not%20to%20be%20confused%20with%20Microsoft%20365)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1412872%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1412872%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676901%22%20target%3D%22_blank%22%3E%40IsaacPiscopo%3C%2FA%3E%26nbsp%3B%20I%20believe%20that%20formula%20was%20also%20incorrect.%26nbsp%3B%20At%20the%20very%20least%20I%20noticed%20you%20had%20the%20same%20error%20looking%20at%20the%20%22right%22%20part%20of%20the%20code%20but%20using%20the%20left%20part%20to%20determine%20the%20number%20of%20characters%20to%20find.%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20said%2C%20I%20think%20the%20problem%20you%20were%20seeing%20was%20something%20with%20your%20'math'.%26nbsp%3B%20I%20didn't%20understand%20what%20math%20you%20were%20exactly%20doing%20so%20I%20also%20changed%20it%20to%20be%20a%20weighted%20average%20for%20each%20color%20portion.%20%26nbsp%3B%3C%2FP%3E%3CP%3EAlthough%20I%20think%20the%20neat%20trick%20that%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3EJos_Woolley%3C%2FA%3E%20used%20could%20have%20made%20the%20formula%20more%20simple%2C%20I%20kept%20your%20format%20since%20that%20is%20what%20you%20understand.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1413230%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1413230%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3Bis%20sort%20of%20works%2C%20it%20just%20doesn't%20work%20sometimes.%20Just%20see%20for%20yourself%20but%20if%20you%20have%20more%20acurate%2C%20more%20complicated%20formulas%20then%20be%20my%20guest...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1413466%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1413466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676901%22%20target%3D%22_blank%22%3E%40IsaacPiscopo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERe%20the%20latest%20file%2C%20which%20of%20the%20results%20in%20there%20are%20not%20as%20you%20would%20wish%20and%2C%20importantly%2C%20what%20%3CEM%3Eshould%3C%2FEM%3E%20they%20be%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%22Trick%22%20is%20a%20bit%20harsh!%20%22Array%20manipulation%22%20would%20be%20my%20preferred%20description!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1413474%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1413474%22%20slang%3D%22en-US%22%3E%3CP%3EI9%20till%20O9%2C%20they%20are%20not%20actually%20displaying%20the%20colour%2C%20but%20they%20are%20giving%20me%20the%20value%20error%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1413540%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1413540%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676901%22%20target%3D%22_blank%22%3E%40IsaacPiscopo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%20Since%20you%20have%20Office%20365%2C%20I'd%20tend%20to%20prefer%20my%20abbreviated%20formula%20set-up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20I8%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E%3DIF(I7%3D%22%22%2C%22%22%2C%22%23%22%26amp%3BCONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7%2C%22%20%22%2CREPT(%22%20%22%2C25))%2C%7B1%2C26%2C51%7D%2C25))%2C2)))%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20then%20amend%20your%20code%20for%20myRGB%20to%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-basic%22%3E%3CCODE%3EFunction%20myRGB(x)%0A%0ADim%20clr%20As%20Long%2C%20src%20As%20Range%2C%20sht%20As%20String%2C%20f%2C%20v%0A%0AIf%20x(1)%20%3D%20%22%22%20Or%20x(2)%20%3D%20%22%22%20Or%20x(3)%20%3D%20%22%22%20Then%0A%20%20%20%20clr%20%3D%20vbWhite%0AElse%0A%20%20%20%20clr%20%3D%20RGB(x(1)%2C%20x(2)%2C%20x(3))%0AEnd%20If%0A%0ASet%20src%3DApplication.ThisCell%0Asht%20%3D%20src.Parent.Name%0A%0Af%20%3D%20%22Changeit(%22%22%22%20%26amp%3B%20sht%20%26amp%3B%20%22%22%22%2C%22%22%22%20%26amp%3B%20src.Address(False%2C%20False)%20%26amp%3B%20%22%22%22%2C%22%20%26amp%3B%20clr%20%26amp%3B%20%22)%22%0Asrc.Parent.Evaluate%20f%0AmyRGB%20%3D%20%22%22%0A%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20which%20the%20formula%20in%20I9%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E%3DmyRGB(TRIM(MID(SUBSTITUTE(I7%2C%22%20%22%2CREPT(%22%20%22%2C25))%2C%7B1%2C26%2C51%7D%2C25)))%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1413954%22%20slang%3D%22en-US%22%3ERe%3A%20Gradient%20Tool%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1413954%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3B%20%22trick%22%20is%20a%20good%20thing%20in%20my%20book.%26nbsp%3B%20I%20want%20to%20learn%20any%20and%20every%20trick%20that%20can%20make%20things%20better.%26nbsp%3B%20Whatever%20the%20term%2C%20it%20is%20a%20great%20technique%20for%20this%20type%20of%20situation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676901%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40IsaacPiscopo%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%20you%20are%20correct%20those%20cells%20aren't%20working%20and%20the%20reason%20is%20the%20exact%20same%20problem%20I%20mentioned%202x%20previously.%26nbsp%3B%20I%20strongly%20suggest%20you%20take%20a%20little%20time%20to%20learn%20why%20they%20aren't%20working.%20%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formula%3A%3C%2FP%3E%3CP%3E%3CFONT%3E%3DIF(%24E%2411%26gt%3B%3DI6-2%2C%20myRGB((LEFT(I7%2C%20SEARCH(%22%20%22%2CI7%2C1)-1))%2C(MID(I7%2C%20SEARCH(%22%20%22%2CI7)%20%2B%201%2C%20SEARCH(%22%20%22%2CI7%2CSEARCH(%22%20%22%2CI7)%2B1)%20-%20SEARCH(%22%20%22%2CI7)%20-%201))%2C%3CFONT%20color%3D%22%23ff0000%22%3E(RIGHT(I7%2C%20SEARCH(%22%20%22%2CI7%2C1)-1)))%2C%3C%2FFONT%3EmyRGB(255%2C%20255%2C%20255))%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3E%3CFONT%20color%3D%22%23ff0000%22%3ERIGHT(I7%2C%20SEARCH(%22%20%22%2CI7%2C1)-1)))%3C%2FFONT%3E%3C%2FFONT%3E%3CFONT%3E%26nbsp%3Bfunction%20will%20look%20at%20the%20string%20found%20at%20I7%20and%26nbsp%3B%3CFONT%3Etake%20the%20right%20most%20X%20characters%20from%20it%20where%20X%20is%20%3CFONT%20color%3D%22%23ff0000%22%3ESEARCH(%22%20%22%2CI7%2C1)-1))%3C%2FFONT%3E.%26nbsp%3B%20But%26nbsp%3B%3CFONT%20color%3D%22%23ff0000%22%3ESEARCH(%22%20%22%2CI7%2C1)-1))%3C%2FFONT%3E%20will%20give%20you%20the%20%23%20of%20characters%20from%20the%20LEFT%20of%20the%20string%20where%20the%20first%20%22%20%22%20is%20found.%26nbsp%3B%20So%20in%20the%20example%20I7%20is%20%22255%2064%208%22%20and%20the%20first%20%22%20%22%20is%20found%20at%20the%204th%20character.%26nbsp%3B%20So%20RIGHT(I7%2C%203)%20is%20%224%208%22%20which%20your%20macro%20doesn't%20understand.%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3CFONT%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello I am using Excel for this and a custom function, my problem is the functions between I8 and AV9 altought some of them appear to work, sometimes they are giving me the Value Error. I think this is happening because when the functions between A7 and AV7 provide the RGB codes they provide 255 8 255 instead of 255 008 255 and I need to add functions to the function to add the 0's.

12 Replies
Highlighted
Best Response confirmed by IsaacPiscopo (New Contributor)
Solution

@IsaacPiscopo  yes sort of.  Your formula for grabbing the RIGHT x characters is looking at how many characters are in the 1st section of the RGB code.  So you had 3 characters for red so it was taking the last 3 characters instead of only 1.  Here is an updated formula:

=IF($E$11>=I6-2,"#"&DEC2HEX((LEFT(I7,SEARCH(" ",I7,1)-1)),2)&DEC2HEX((MID(I7,SEARCH(" ",I7)+1,SEARCH(" ",I7,SEARCH(" ",I7)+1)-SEARCH(" ",I7)-1)),2)&DEC2HEX((RIGHT(I7,LEN(I7)-SEARCH(" ",I7,SEARCH(" ",I7)+1))),2),(""))

 

I also updated the initial condition to $E$11 and compared it to the numbers above it I6-2 so it would be easy to copy/fill and have excell automatically update all the cells.  That said, you MERGED all those cells which disables may useful things in excel.  If you want wider cells I recommend just highlighting those columns and dragging them wider or right click and select 'column width'

I attached an updated file but never enabled the macros so they may or may not be attached.

Highlighted

@IsaacPiscopo 

 

Just to add to @mtarler's post, if you have Office 365 then the formula in I8 can be abbreviated to:

 

=IF($E$11>=I6-2,"#"&CONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7," ",REPT(" ",25)),{1,26,51},25)),2)),"")

 

Regards

Highlighted

@Jos_Woolley  Even without the new array functionality you might be able to use that as an array function or at least use that trick to "simplify" the existing formula to avoid the search of search - search ...

=IF($E$11>=I6-2,"#"&CONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7," ",REPT(" ",25)),1,25))&CONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7," ",REPT(" ",25)),26,25))&CONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7," ",REPT(" ",25)),51,25)),2)),"")

Highlighted

@mtarler 

 

Actually my suggestion had nothing to do with the new array functionality within 365. It works as a standalone, single-cell formula. My reference to 365 was based solely on the use of the CONCAT function, which I (perhaps erroneously?) believed was only available to 365 subscribers.

 

P.S. Why are you using CONCAT in the construction you posted? Seems superfluous to me.

 

Regards

Highlighted

@Jos_Woolley  you are correct. 

1) I didn't recognize any of those functions as inherently being array functions that would work with that array input {1, 26, 51} but apparently it works fine (at least in my excel)

2) concat is wasteful because I was lazy and quick to copy and paste your formula 3 times to avoid the {…} array portion.

 

Highlighted

So I changed all the boxes and yeah now the RGB box is broken. I also changed the * part to cell boxes so its better for autofill.(btw I'm using Microsoft Office 365, not to be confused with Microsoft 365)

Highlighted

@IsaacPiscopo  I believe that formula was also incorrect.  At the very least I noticed you had the same error looking at the "right" part of the code but using the left part to determine the number of characters to find. 

That said, I think the problem you were seeing was something with your 'math'.  I didn't understand what math you were exactly doing so I also changed it to be a weighted average for each color portion.  

Although I think the neat trick that  Jos_Woolley used could have made the formula more simple, I kept your format since that is what you understand.

Highlighted

@mtarler is sort of works, it just doesn't work sometimes. Just see for yourself but if you have more acurate, more complicated formulas then be my guest...

Highlighted

@IsaacPiscopo 

 

Re the latest file, which of the results in there are not as you would wish and, importantly, what should they be?

 

@mtarler "Trick" is a bit harsh! "Array manipulation" would be my preferred description!

Highlighted

I9 till O9, they are not actually displaying the colour, but they are giving me the value error

 

Highlighted

@IsaacPiscopo 

 

Thanks. Since you have Office 365, I'd tend to prefer my abbreviated formula set-up.

 

In I8:

 

=IF(I7="","","#"&CONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7," ",REPT(" ",25)),{1,26,51},25)),2)))

 

I'd then amend your code for myRGB to:

 

Function myRGB(x)

Dim clr As Long, src As Range, sht As String, f, v

If x(1) = "" Or x(2) = "" Or x(3) = "" Then
    clr = vbWhite
Else
    clr = RGB(x(1), x(2), x(3))
End If

Set src=Application.ThisCell
sht = src.Parent.Name

f = "Changeit(""" & sht & """,""" & src.Address(False, False) & """," & clr & ")"
src.Parent.Evaluate f
myRGB = ""

End Function

 

After which the formula in I9 is:

 

=myRGB(TRIM(MID(SUBSTITUTE(I7," ",REPT(" ",25)),{1,26,51},25)))

 

Regards

Highlighted

@Jos_Woolley  "trick" is a good thing in my book.  I want to learn any and every trick that can make things better.  Whatever the term, it is a great technique for this type of situation.

 

@IsaacPiscopo  you are correct those cells aren't working and the reason is the exact same problem I mentioned 2x previously.  I strongly suggest you take a little time to learn why they aren't working.  

Your formula:

=IF($E$11>=I6-2, myRGB((LEFT(I7, SEARCH(" ",I7,1)-1)),(MID(I7, SEARCH(" ",I7) + 1, SEARCH(" ",I7,SEARCH(" ",I7)+1) - SEARCH(" ",I7) - 1)),(RIGHT(I7, SEARCH(" ",I7,1)-1))),myRGB(255, 255, 255))

 

RIGHT(I7, SEARCH(" ",I7,1)-1))) function will look at the string found at I7 and take the right most X characters from it where X is SEARCH(" ",I7,1)-1)).  But SEARCH(" ",I7,1)-1)) will give you the # of characters from the LEFT of the string where the first " " is found.  So in the example I7 is "255 64 8" and the first " " is found at the 4th character.  So RIGHT(I7, 3) is "4 8" which your macro doesn't understand.