Macro for Ageing and count

[Vijayarc]

Hi Ninja Team

as per my attached sheet , i want to calculate ageing and count of each ageing,
i tried in all form but i cant solve it
i have more 90,000 rows like this i given sample only,
sheet1:
--------
required macro to calculate ageing bucket for number in A column
P1 P1 Ageing
25 30-90
21 0-30
44 30-90
40 30-90
38 30-90
12 0-30
9 0-30

sheet2:
----------
the count of each ageing bucket

Ageing Count
P1 0-30
30-90
90-180
180-360
>360

please help ninja team and needful

 

[vletm]

Vijayarc
You could use one from this ...
https://chandoo.org/forum/threads/if-and-formula-with-multiple-condition.39413/

 

[Vijayarc]

Hi Vletm

sheet1:
---------
i got formula for ageing bucket, and works well,

but i am facing issue is,, if i implement vlookup in vba the formula is not fills up to end of row, my columns are in dynamic range, i cant fix in range. please help

sheet2:
count formula is not working for >360 ( i do no why) please help!!!

Sub Ageing_P1()
Dim LastRowColumnC As Long
LastRowColumnC = Range("G" & Rows.Count).End(xlUp).Row
Range("G2:G250" & LastRowColumnC).Formula = "=VLOOKUP(RC[-6],R3C20:R7C21,2,1)"

ThisWorkbook.Save

End Sub

 

[vletm]

Sheet1:
You have mistakes even with 'buckets'.
Did You really even try to do it with
https://chandoo.org/forum/threads/if-and-formula-with-multiple-condition.39413/?

Sheet2:
If You mean Ageing_P1 ... then would You explain, what should it do?
Have tried to make it ... dynamic or what?

> I'm facing same issues as with previous cases ...

 

[Vijayarc]

Sheet1:
yes i tried with your lookup formula instead of vlookup - its works

Sheet2:
under Ageing P1- i want count of each bucket - i am using countif formula to count , but count is not happening for >360 bucket - it shows only 0

Have tried to make it ... dynamic or what? - yes sir i tried with VBA - but formula is not auto filling till to end of row - so count is also mismatch

 

[vletm]

Sheet1:
As written

Sheet2:
https://support.office.com/en-us/article/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34
( Even '>360' works as it should work ...)

Dynamics:
Formulas works correct, if You would 'tell' to formula as needed.

You should try to learn to study ..

It won't help You, if I would try to give 'even needed' solution,
because You have so far always add something else after that ...
That's why I tried to give sample (that link) which would help You,
but ...