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.

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

  • mtarler's avatar
    mtarler
    Silver Contributor
    May 23, 2020

    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.

     

     

     

Resources