Replace data with other data in Excel

V

viriiman

In excel, I'm trying to replace a numeric value with another value
only in certain cells. For example, in cells C11 to C30, if I type in
the number one, I want that to be replaced with the number 28149. If I
type in the number two, I want that to be replaced with the number
28150 (etc). I don't want to use the replace option in excel
(Find...Replace), I would like to have this done automatically.

Please let me know what I need to do, and please go slow. I'm new to
all this.

Thanks.

Viriiman
 
T

Trevor Shuttleworth

Right click on the sheet tab for the worksheet where you want this to
happen.

Copy and paste the following code into the module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C11:C30")) _
Is Nothing Then Exit Sub
If Target.HasFormula = True Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
If Target.Value = 0 Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value + 28148
Application.EnableEvents = True
End Sub

I've tried to cater for the obvious (to me) pitfalls but this may not cope
with everything you throw at it. Give it a try.

Regards

Trevor
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top