Replace a number with text if it lies within a range/group

[BPSJACK]

Hi, I'm trying to build a macro that will replace a number in a cell with a letter if it falls within a specific range. For example.

I have a list of numbers in Column A which will fall into the following ranges:

1 - 9 = A
10 -19 = B
20 -29 = C

And if they fall within that range the number would be replaced with a letter.

Any ideas how I might achieve this?

[Winon]

Hello BPSJACK,

Your question is wide open to a lot of assumptions as to exactly how your spreadsheet is set up.

Show us what you have, by uploading a sample workbook, and what result/s you desire.

[BPSJACK]

Maybe my wording was not so great, hopefully I can illustrate it a little better.

So using the following rules

Range	Designation
1-9	A
10-19	B
20-29	C

Column A:

9

15

28

7

11

would become:

A

B

C

A

B

[BPSJACK]

The solution I've used is:

Please Login or Register  to view this content.

Not the most efficient I'm sure, but it works nevertheless.

[Winon]

Hello BPSJACK,

Or you could use this alternative,

Please Login or Register  to view this content.

[Winon]

Or if you prefer the Case way, you could use,

Please Login or Register  to view this content.

[oeldere]

(without VBA) with VLookup.

See the attached file.

Thanks Marcol.

It seems the attachement wasn't uploaded!!

[Marcol]

Or if you prefer the Case way, you could use ...

Try writing your Select statement like this

Please Login or Register  to view this content.

The Worksheet_Change should temporarily disable events to avoid calculation loops.
i.e.
Application.EnableEvents = False/True

[Winon]

@ Marcol,

I like that. Thank you!

[jindon]

Or

Please Login or Register  to view this content.

[Marcol]

Or

Please Login or Register  to view this content.

Hmm?
Looks neat, but why introduce a loop? is it really required...

[jindon]

Hmm?
Looks neat, but why introduce a loop? is it really required...

You will see the difference when you enter/paste multiple cells in Col.A.

[Winon]

@ jindon,

I cannot get your Code to work on my side.

@ Marcol,

And so we both forgot to add the "On Error Resume Next" line.

Without it, highlight a Range in Column A, hit delete, and "Type Mismatch" Error appears.

[jindon]

Working......

[Winon]

@ jindon,

Thank you for sending me the WorkBook. It did not work on my side. Office 2007.

Excel lost its mind! Exited completely, opened Excel again, and yes, now it works!

Very sorry about that.

Very impressive coding!

[Marcol]

You will see the difference when you enter/paste multiple cells in Col.A.

Apologies, I must stop trying to read posts after leaving the pub ...

Nice formula

HLOOKUP() and VLOOKUP() were introduced, I think in 2003, maybe for all versions this line would be safer, if there are any oldies still using '97, and hey, it's shorter ...

Please Login or Register  to view this content.

[jindon]

V/HLookup function is available since xl 5.0 so no problem.

[Winon]

And to cater for Numbers/Data to be entered which do not fall within a range/group, I changed jindon's code as follows:

Please Login or Register  to view this content.

Hope you do not mind jindon.

[jindon]

If you do like that, you also need to care about numbers

1) below <1
2) with decimal

[Winon]

@ jindon

Hi jindon,

Thank you for your reply.

If you do like that, you also need to care about numbers

1) below <1
2) with decimal

The Code as it is handles that just fine.