Forum Discussion

IsaacPiscopo's avatar
IsaacPiscopo
Copper Contributor
May 22, 2020
Solved

Gradient Tool

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 ...
excel
Formulas and Functions
Macros and VBA
  • mtarler's avatar
    mtarler
    May 22, 2020

    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.

mtarler's avatar
mtarler
Silver Contributor
May 22, 2020

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.

  • Jos_Woolley's avatar
    Jos_Woolley
    Iron Contributor
    May 23, 2020

    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! 🙂

    • IsaacPiscopo's avatar
      IsaacPiscopo
      Copper Contributor
      May 23, 2020

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

       

      • Jos_Woolley's avatar
        Jos_Woolley
        Iron Contributor
        May 23, 2020

        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

Resources