Page 2 of 2 FirstFirst 12
Results 11 to 11 of 11

Thread: UDF that can change values in cells other than the cell in which the UDF is used

  1. #11
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,422
    Rep Power
    10
    “AdeelSolution2” ( )
    (A Final Simplified Solution)


    There is not much to do.

    I am not interested in the code lines to put the result in a different cell , ( although they are still working : )

    So this looks like our final solution.

    Code:
    '   '   From  Adeel  spreadsheet    '  =sum_color(D6:G15;C17)    '    http://www.eileenslounge.com/viewtopic.php?p=300075#p300075
    ' First thing
    Function DoSomeColor(ByVal RngA As Range, ByVal RngB As Range) As String
     Dim vTemp As Variant
     Let vTemp = Evaluate("='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!DisplayFormatUDF.SomeColor(" & RngA.Address & ", " & RngB.Address & ")")      '
    ' Let vTemp = Evaluate("SomeColor(" & RngA.Address & ", " & RngB.Address & ")")  ' Shortened version relying on default
     Let DoSomeColor = vTemp
    End Function
    
    
    ' Second thing
    Function SomeColor(ByVal RgA As Range, RgB As Range) As String
    Stop  '  It wont
    Dim Vee As Long, Sea As Range
        For Each Sea In RgA
            If Sea.DisplayFormat.Interior.ColorIndex = RgB.DisplayFormat.Interior.ColorIndex Then
             Let Vee = Vee + Sea.Value
            Else
            End If
        Next Sea
    ' Let RgB.Offset(1, 2).Value = ""
    ' Let RgB.Offset(1, 2).Value = Vee
     Let SomeColor = Vee
    End Function
    
    That’s it


    Here is a simplified version for clarity to post in a forum Thread
    Code:
    '   '                          https://www.excelfox.com/forum/showthread.php/2831-UDF-that-can-change-values-in-cells-other-than-the-cell-in-which-the-UDF-is-used?p=18456&viewfull=1#post18456          https://www.excelfox.com/forum/showthread.php/2831-UDF-that-can-change-values-in-cells-other-than-the-cell-in-which-the-UDF-is-used/page2#post18456
    ' First thing
    Function DoSum_Colour(ByVal RngA As Range, ByVal RngB As Range) As String       '    From  Adeel  spreadsheet    '  =sum_color(D6:G15;C17)    '    http://www.eileenslounge.com/viewtopic.php?p=300075#p300075
     Let DoSum_Colour = Evaluate("Sum_Colour(" & RngA.Address & ", " & RngB.Address & ")")
    End Function
    ' Second thing
    Function Sum_Colour(ByVal RgA As Range, RgB As Range) As String
    Dim Vee As Long, Sea As Range
        For Each Sea In RgA
            If Sea.DisplayFormat.Interior.ColorIndex = RgB.DisplayFormat.Interior.ColorIndex Then
             Let Vee = Vee + Sea.Value
            Else
            End If
        Next Sea
     Let Sum_Colour = Vee
    End Function
    Last edited by DocAElstein; 10-22-2022 at 03:12 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Question on UDF LookUpConcat
    By K2` in forum Excel Help
    Replies: 4
    Last Post: 05-07-2013, 10:25 PM
  2. Trouble implementing UDF's
    By ProspectiveCounselor in forum Excel Help
    Replies: 4
    Last Post: 05-06-2013, 08:07 PM
  3. Insert Picture in a Cell UDF
    By Admin in forum Download Center
    Replies: 10
    Last Post: 12-07-2012, 04:49 PM
  4. UDF to Create In-Cell Chart in Excel
    By Admin in forum Download Center
    Replies: 0
    Last Post: 08-13-2011, 09:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •