VBA to replace multiple values with specific labels

[johnatas]

Hi there, I was wondering if there's any VBA to replace multiple values in a column with specific labels?

The issue I have is a column of data named "Age" with values like 24,25,26,27,37,44,45 etc. I manually categorized them into categories like "25-30" and "40&above" for the corresponding ages.

However, as I'm dealing with a large number of entries, I was wondering if there is a VBA to automate the replacement process?

E.g. creating a button that would convert new data where the ages are 25, 26, 27 to "25-30" and 44,46,49 to "40&above"

Many thanks for any advice!

[Kaper]

Why VBA?
I'd honestly recommend use of Vlookup function like the one in attached file.

[Kaper]

Of course you can do this with VBA but then we have too little information. for instance selec a range with such numbers and run for instance such macro:

Sub test()
Dim cell As Range
For Each cell In Selection
 If IsNumeric(cell) Then
   cell = Application.WorksheetFunction.VLookup(cell, Sheets("Arkusz1").Range("I2:J7"), 2, True)
 End If
Next cell
End Sub

try selecting B2:B4 in attachment from previous post and run it. of course after such macro formula in column C will return errors

[Olly]

I'd suggest first creating a 'lookup' table, which maps ages to your required groups:

	A	B
1	Age	Age Group
2	24	20-24
3	25	25-29
4	26	25-29
5	27	25-29
6	28	25-29
7	29	25-29
8	30	30-34
9	31	30-34
10	etc

Then you can use a bit of code to replace age values with age group values:

Sub foo()
    Dim c As Range, wsLookup As Worksheet, rngLookup As Range
    
    'change these values to match your lookup table
    Set wsLookup = Worksheets("Lookup Sheet")
    Set rngLookup = wsLookup.Range("A1:B100")
    
    For Each c In Selection 'change as necessary
        c.Value = rngLookup.Find(what:=c.Value, lookat:=xlWhole).Offset(0, 1).Value
    Next c
End Sub

[JOHN H. DAVIS]

If you "AGE" Column were Column A.

Sub johnatas()
Dim i As Long
For i = Range("A" & Rows.count).End(3).Row To 2 Step -1
    Select Case Range("A" & i).Value
        Case Is > 40
            Range("A" & i).Value = "40 & above"
        Case Is > 24, Is < 31
            Range("A" & i).Value = "25-30"
    End Select
Next i
End Sub

[johnatas]

Sorry for the delayed reply but many thanks everyone, this worked wonders!