Numbers in different areas on page? Part 2

[z12ent]

Hello I have a question? In Excel I have list of the same numbers but in different areas on my sheet. Example 1 2 3 4 5. Now if I wanted all the 1's to change if I entered 6 in place of 1 what formula would I use rather than going through the entire page changing all the 1's to 6's?

 

[mrshl9898]

Press ctrl + H to replace everything on the sheet.

The formula would be =SUBSTITUTE(A1,1,6)

 

[z12ent]

Will try that. Thank you for your help.

 

[jim may]

Another approach using VBA event, no formula.

Paste this code into your SHEET MODULE -- And Change Line 2 of Code to cover the Range to be Included/Covered.

Private Sub Worksheet_Change(ByVal Target As Range)
Set Rng = Range("B6:F15")   'CHANGE THIS RANGE AREA TO YOUR RANGE AREA BEFORE ALLOWING TO RUN
Application.EnableEvents = False
Ent = Target.Value
Application.Undo
Bef = Target.Value
With Rng
    For Each C In .Cells
        If C = Bef Then
            C.Value = Ent
        End If
    Next C
End With
Application.EnableEvents = True
End Sub

 

[z12ent]

Hi Jim, thanks for the reply but is that code for excel or VB?

Just want to change all the numbers in excel when I change the main number? The first row will always remain the same, it's the second row that will always change and I'd like for the change to happen to all the same / related fields under 1 or 2 or 3 or 4 or 5. Example:

1 2 3 4 5
21 22 16 44 55

 

[jim may]

Are you familiar with the VB Editor within Excel? The code I provided gets posted in the code sheet of the same sheet you are working with, NOT A STANDARD MODULE.

Im not understanding the 21, 22, 16, 44, 55. What were there values originally, if these are the values after the changes...

 

[z12ent]

Jim,

Sorry I'm new to excel so my verbiage my not be up to par. I didn't know where to place the code you provided so when doing a google search VB poped up. I'd upload you a photo to better explain what I'm trying to achieve but since it's not attached to a url it won't allow me to do so. So to better explain what I'm trying to have the program do is when i replace a number that is repeated in multiple areas i want all those related numbers to change as well.