• 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...

  • 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

S

S. Das

Active Member
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.
 

Attachments

  • Sample (4).xlsm
    20.4 KB · Views: 3
Try changing the function to:
Code: 
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?
 
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.
 
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: 
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
 

Attachments

  • Chandoo36866Sample.xlsm
    654.5 KB · Views: 1
Last edited:
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
 
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.
 
p45cal said:
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.
Click to expand...
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.
 
You must log in or register to reply here.
Back
Top