Macro to create age bands from an age field | Excel forum

Hi Sarah, thanks for your query. It's a little difficult to create something specific without seeing your source data, but the following code should give you a few pointers on how to achieve the results you want.

First of all, select the cells with the ages in, then run the code. It will loop through the selected cells, categorising them and writing the categories into Column A of Sheet2. I've only put two categories into the code, you will have to complete the rest. It's very rough and ready, and I would probably develop this by switching to using Select Case. I would also customise to your data by writing the categories into the specific column and worksheet you want. Anyway, here is the code:

*****

Sub categorise_me()

Dim curcell As Range
Dim cellcount As Integer
cellcount = 1


For Each curcell In Selection

If curcell.Value > 0 And curcell.Value < 5 Then

Sheets("Sheet2").Range("a1").Cells(cellcount, 1).Value = "0-4"
cellcount = cellcount + 1

End If


If curcell.Value > 5 And curcell.Value < 10 Then

Sheets("Sheet2").Range("a1").Cells(cellcount, 1).Value = "5-10"
cellcount = cellcount + 1

End If



Next curcell

End Sub

*****

Hope this helps,

Anthony