# Custom function not updating properly

#### 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

• 20.4 KB Views: 3

#### p45cal

##### Well-Known Member
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?

#### p45cal

##### Well-Known Member
Thankyou.
Now did it solve your problem?!

#### S. Das

##### Active Member
Thank you so much. Your code is working perfectly

#### NARAYANK991

##### Excel Ninja
Hi ,

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

Narayan

#### NARAYANK991

##### Excel Ninja
Hi ,

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

Narayan

#### S. Das

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

#### p45cal

##### Well-Known Member
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

• 654.5 KB Views: 1
Last edited:

#### S. Das

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

#### p45cal

##### Well-Known Member
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.

#### S. Das

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