Changing cell values

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
Is it possible to change cell values according to the last cell value eg:

I have a row of cell say A10:Z10 in these cells different letters are entered say either H, R or W is it possible that if I imput the letter S in a cell that every cell to the right of that S which contains a W will change to S, and vice versa ?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Yes. It's possible. First, what do you mean by "vice versa"? Does that mean S turns to W? What about the letters H and R? Are they supposed to do anything?

Do you feel comfortable with a macro solution or do you want some kind of formula?

Dufus
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
Yes vice versa means S then turns to W, the letters H & R remain as they are so you may have :

A B C D E F G H I J K L M
row10 w h w w w w r r w h w w r

which if enter S in D10 would become :

A B C D E F G H I J K L M
ROW10 W H W S S S R R S H S S R

I would prefer a formula but if it has to be a macro I will try to work around that.
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
Sorry that didnt come out how I hoped can you see what I mean the columns A:M should have been alligned above the relevant columns in row 10
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Hello Peter1973,
I'm not sure off hand how that would be done with formulas as they are not my
strong suit. It wouldn't surprise me if someone here could show how it's done
but it's hard for me to imagine it would be less complicated than a vba solution.
You say 'if it has to be a macro I will try to work around that' so here are some
pretty detailed instructions to do what I'm thinking.
This sample only deals with row 10 in columns A:Z as per your posted example.
If that's just an example then post back with the rows & columns you really
want it to work on and we can adjust it to suit.

1) Go into the vb editor (Press Alt+F11)
2) Show the Project Explorer window (Press Ctrl+R)
3) In the project explorer, locate the name of the workbook and right click it.
Choose Insert > Module.
4) Now type (or paste) this into the white area that is the new module you just inserted.
Public OldVal As String
5) Next, in the project explorer locate the sheet you're working with and double click it.
(This will put you into the sheet code module for that sheet.)
6) Copy & paste this code into that module.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'work only on single cell selections
If Target.Column > 26 Then Exit Sub 'work only on columns A:Z
If Target.Row <> 10 Then Exit Sub ' work only on row 10
OldVal = Target.Value
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'work only on single cell changes
If Target.Column > 26 Then Exit Sub 'work only on columns A:Z
If Target.Row <> 10 Then Exit Sub ' work only on row 10
Dim ThisCol As Integer, Col As Integer
ThisCol = Target.Column
If ThisCol < 26 Then
  For Col = ThisCol To 26
    If Cells(10, Col).Value = OldVal Then _
      Cells(10, Col).Value = Target.Value
  Next Col
End If
End Sub
7) Press Alt+Q to close the vbe and get back to your sheet to try it out.
Post back with any changes you'd like to make.
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Peter1973,

For a formula solution, try this formula in cell A11:
=IF(AND(COUNTIF($A$10:A10,"S")>0,A10="W"),"S",A10)

Copy the formula over to column Z.

This new row will reflect the values you want after the change is made.

Dufus
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Dufus,
This looks like it would work well for the column of the change but what about all
the columns to the right that contain the same value as the changed cell's original
value?
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
Thanks HalfAce I couldnt go wrong with them instructions, just one quick question I have altered the code slightly so that it includes different columns ie D:BB which I can do but im having am issue trying to change it to include more rows ie 10:256 I think my error is in the line IF cells(10,col) or around there.

any ideas
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello Peter1973,
I think my error is in the line IF cells(10,col)
Yes, that's correct. To make it work on variable rows as well we'll need to make
a few changes. (Use a variable for the rows too.)
Try replacing your existing code with this and see if it's what you're after.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'work only on single cell selections
If Intersect(Target, Range("D10:BB256")) Is Nothing Then Exit Sub 'work only on range D10:BB256
OldVal = Target.Value
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'work only on single cell changes
If Not Intersect(Target, Range("D10:BB256")) Is Nothing Then 'work only on changes in range D10:BB256
  Dim ThisCol As Integer, Col As Integer, ThisRow As Long
  ThisCol = Target.Column
  ThisRow = Target.Row
  If ThisCol < 54 Then
    For Col = ThisCol To 54
      If Cells(ThisRow, Col).Value = OldVal Then _
        Cells(ThisRow, Col).Value = Target.Value
    Next Col
  End If
End If
End Sub
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
That works great but i have hit one snag prehaps I should have mentioned it every so often there may be a time value put in on of the cells ie 21:00 when I put this in it seems to throw off all the cells with a "w" or "s" in them.

Sorry should have included this in first post.
 

Forum statistics

Threads
1,140,941
Messages
5,703,292
Members
421,290
Latest member
java

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top