1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Custom function not updating properly

Discussion in 'Ask an Excel Question' started by S. Das, Jan 4, 2018.

  1. S. Das

    S. Das Member

    Messages:
    88
    I have a custom function for grouping of numbers named GroupN, which is given by John Jairo V sir.

    In the attached sample file there is two sheet, sheet1, and sheet3. In sheet1 column A some data is present. If the value in column B is 1 then the value of column A copied to column C and the data in column C are grouped in column D. this part is working very nice.

    There is another part, If the value in column B is 1 then the value of column A copied to column A of sheet3 and grouped in column B. This time grouping is not always updated. Double click the column B and pressing Enter can make it work.

    And the 2nd problem is some time column D of sheet1 and column B of sheet 3 becomes blank when it must show some value.

    Attached Files:

  2. p45cal

    p45cal Well-Known Member

    Messages:
    1,108
    Try changing the function to:
    Code (vb):
    Function GroupN$(S As Range)
    Dim a, i&, R$, p@
    a = Application.Transpose(S.Value)
    b = a
    j = 0
    For i = LBound(b) To UBound(b)
      If Not (IsEmpty(b(i)) Or Len(b(i)) = 0) Then
        j = j + 1
        a(j) = b(i)
      End If
    Next i
    ReDim Preserve a(1 To j + 1)
    a(UBound(a)) = 0: p = a(1)
    For i = LBound(a) To UBound(a) - 1
      If a(i + 1) - a(i) <> 1 Then
        If p = a(i) Then
          R = R & "," & a(i)
        Else
          R = R & "," & p & "-" & a(i)
        End If
        p = a(i + 1)
      End If
    Next i

    GroupN = Mid(R, 2): Erase a: Erase b
    End Function
    I stepped through the code and the Evaluate line wasn't bringing back the right values; it was as if the udf was calculating before the range in the argument had updated. I've tweaked it not to use Evaluate.

    Could you point me to where John Jairo posted this code?
    Thomas Kuriakose likes this.
  3. S. Das

    S. Das Member

    Messages:
    88
  4. p45cal

    p45cal Well-Known Member

    Messages:
    1,108
    Thankyou.
    Now did it solve your problem?!
  5. S. Das

    S. Das Member

    Messages:
    88
    Thank you so much. Your code is working perfectly
  6. S. Das

    S. Das Member

    Messages:
    88
  7. S. Das

    S. Das Member

    Messages:
    88
    Anyone, please help me...
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    Please explain what the problem is ,and upload a file with data which illustrates the problem.

    Narayan
  9. S. Das

    S. Das Member

    Messages:
    88
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    Has the problem you explained there not yet been solved by p45cal ?

    Narayan
  11. S. Das

    S. Das Member

    Messages:
    88
    One problem is solved by p45cal but there is another problem and this problem is in my workbook only.
    If I have data like this:
    4161247464
    4161247465
    4161247466
    4161247467
    4161247468
    4161247469
    4161247470

    Then desired output is : 4161247464 - 4161247470
    But output given by the code is: 4161247464 - 4161247466, 4161247467 - 4161247469, 4161247470 like this.
  12. p45cal

    p45cal Well-Known Member

    Messages:
    1,108
    I'm not sure what's going on but it appears that your values in column E of Sheet2 are not whole numbers; In the attached I inserted a new column F to give the integers of column E and when the GroupN formula is applied to that range it gives the desired results. So if you were to add INT() around that whole formula in column E it might work.
    Otherwise, a tweak to the GroupN function might be to round the incoming data to integers within it (this wouldn't affect the values in column E on the sheet).
    Here's a GroupN2 function which seems to work:
    Code (vb):

    Function GroupN2$(S As Range) 'name change
    Dim a, i&, R$, p@
    a = Application.Transpose(S.Value)
    b = a
    j = 0
    For i = LBound(b) To UBound(b)
      If Not (IsEmpty(b(i)) Or Len(b(i)) = 0) Then
      j = j + 1
      a(j) = Round(b(i)) 'this line changed
     End If
    Next i
    ReDim Preserve a(1 To j + 1)

    a(UBound(a)) = 0: p = a(1)
    For i = LBound(a) To UBound(a) - 1
      If a(i + 1) - a(i) <> 1 Then
      If p = a(i) Then
      R = R & "," & a(i)
      Else
      R = R & "," & p & "-" & a(i)
      End If
      p = a(i + 1)
      End If
    Next i

    GroupN2 = Mid(R, 2): Erase a: Erase b 'name change
    End Function

    Attached Files:

    Last edited: Jan 12, 2018
  13. S. Das

    S. Das Member

    Messages:
    88
    I think it is working (GroupN2) but let me check it in details and I will inform you. As one of my family members is hospitalized, so little busy with that

    Thanks a lot
  14. p45cal

    p45cal Well-Known Member

    Messages:
    1,108
    As an aside, your WARNING sheet contains advice which exposes users to malware; you advise them to Enable all macros, which choice Microsoft itself says 'not recommended; potentially dangerous code can run'.
    I would advise you that you advise them to choose 'Disable all macros with notification'. This will allow them to enable macros on a file by file basis. Much safer.
  15. S. Das

    S. Das Member

    Messages:
    88
    Thank you for your advice. I will keep it in mind in future.

    And thank you for your help and support regarding this problem. and your code is working perfectly.

Share This Page