How to use user defined function (bucket)

[Beatrix]

Hi All ,

I have below code to group number of days into the buckets. If the number of days is between 11-30 then it should return 1 month, if it's between 31-90 then 3 months etc..

I couldn't work it out which goes to D2m and which goes to D2Ms

Would you mind to have a look at the udf and give me an example please?

Thanks very much

Function Bucket(d2m As Integer, D2Ms As Variant) As Variant

   
      Dim Fnd_bckt As Boolean

      Dim d2m_cnt As Integer
   
      Fnd_bckt = False
      d2m_cnt = 1
   
      While Not Fnd_bckt And d2m_cnt <= 6
          Fnd_bckt = (d2m > D2Ms(d2m_cnt, 1)) And (d2m <= D2Ms(d2m_cnt, 2))
          d2m_cnt = d2m_cnt + 1
      Wend
      If Fnd_bckt Then
          Bucket = D2Ms(d2m_cnt - 1, 3)
      End If
   
  End Function

[Jan Karel Pieterse]

Why use a UDF, you can utilise VLOOKUP.

Suppose a table like this (starting in cell A1):
Days Months
0 1
30 2
90 3
180 6

And you have a number of days in cell F1, then this formula gives the number of months corresponding to the table.

=VLOOKUP(F1,$A$1:$B$10,True)

[Kenneth Hobs]

Can you explain more? What are you needing done? I am not sure if you are working with dates or not. Try showing us some example data and expected result. Returning 1 or 3 months is not understood. We can return the integer 1 or 3. I don't see a use for D2Ms().

Taken literally, you just want to send an integer of 11 to 90 and return 1 or 3. Obviously, you need to handle the less than 11 and more than 90 cases or text entries or just let it error like any function.

If you are working with a date1 and a date2 and looking at the number of days difference then VBA's DateDiff() can be used to get the days difference or just substract the two dates to get the days difference.

[Beatrix]

Thanks very much for your reply and for your time Mr Hobs. I really appreciate it.

I was trying to help someone else so there was a misunderstanding about the bucket list. Finally I worked it out how to use the udf bucket after she gave me the right bucket list. Next time I'll ask for the sample data at the beginning. I couldn't see a use for D2Ms too before she sent me her data but I now understand bucket range goes to D2Ms and number of days for due date goes to D2m. I attached the spreadsheet for an example.

Thanks very much again.

Can you explain more? What are you needing done? I am not sure if you are working with dates or not. Try showing us some example data and expected result. Returning 1 or 3 months is not understood. We can return the integer 1 or 3. I don't see a use for D2Ms().

Taken literally, you just want to send an integer of 11 to 90 and return 1 or 3. Obviously, you need to handle the less than 11 and more than 90 cases or text entries or just let it error like any function.

If you are working with a date1 and a date2 and looking at the number of days difference then VBA's DateDiff() can be used to get the days difference or just substract the two dates to get the days difference.

[Beatrix]

Hi Jan ,

Thanks very much for your reply. I got this query from someone else and was asked to find out how udf works. That's why use a UDF. tHANKS again.

Why use a UDF, you can utilise VLOOKUP.

Suppose a table like this (starting in cell A1):
Days Months
0 1
30 2
90 3
180 6

And you have a number of days in cell F1, then this formula gives the number of months corresponding to the table.

=VLOOKUP(F1,$A$1:$B$10,True)