Changing cell values

[Peter1973]

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 ?

 

[Dufus]

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]

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]

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]

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.

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]

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]

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]

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]

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.

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]

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.