Swap Cells based on Cell value

[RedOctoberKnight]

Good Morning,

I was wondering if anyone had any ideas on how to swap the contents of two cells based on 3rd cells value?

For example Currently: A1=CAT, B1=DOG. If C1 ="SWITCH", THEN A1=DOG AND B1=CAT.

If C1 Changes again to something other than "switch" , then A1=CAT and B1=DOG again....

Any help would be much appreciated.

Thanks,

 

[Joe4]

A cell can either have in it a:
1. hard-coded value
- or -
2. a formula
but NEVER both at the same time.

So, if you have hard-coded values in cells A1 and B1, the only way to get them to "switch" values is to use VBA.

If you are open to using VBA, then the question is how/when you want this VBA code to run.
Do you want:
1. someone to run it manually
- or -
2. it to run automatically upon some event happening, such as someone manually entering a specific value in cell C1?

If you can tell us how you want that happen, we can come up with VBA code to do what you want.

 

[DanteAmor]

Try the following formulas using auxiliary cells F1 and G1:

varios 17nov2023.xlsm
	A	B	C	D	E	F	G
1	CAT	DOG	SWITCH			CAT	DOG
Hoja3

Cell Formulas
Range	Formula
A1	A1	=IF(C1="SWITCH",F1,G1)
B1	B1	=IF(C1="SWITCH",G1,F1)

 

[RedOctoberKnight]

A cell can either have in it a:
1. hard-coded value
- or -
2. a formula
but NEVER both at the same time.

So, if you have hard-coded values in cells A1 and B1, the only way to get them to "switch" values is to use VBA.

If you are open to using VBA, then the question is how/when you want this VBA code to run.
Do you want:
1. someone to run it manually
- or -
2. it to run automatically upon some event happening, such as someone manually entering a specific value in cell C1?

If you can tell us how you want that happen, we can come up with VBA code to do what you want.

I am open to using VBA, but it's still very new to me. If I could have it run automatically any time the value in C is changed to "switch", that would help immensely.

Thanks,

 

[Joe4]

Right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code into the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim tempA As String
    Dim tempB As String

'   See if any cells in column C manually updated
    Set rng = Intersect(Target, Columns("C:C"))
    
'   Exit if no cells in column C updated
    If rng Is Nothing Then Exit Sub
    
'   Check updated entry in column C
    For Each cell In rng
        If cell.Value = "Switch" Then
'           Swap values
            Application.ScreenUpdating = False
            tempA = Cells(cell.Row, "A")
            tempB = Cells(cell.Row, "B")
            Cells(cell.Row, "A") = tempB
            Cells(cell.Row, "B") = tempA
            Application.ScreenUpdating = True
        End If
    Next cell
    
End Sub

Now, whenever the value "Switch" is entered into column C, it will automatically flip the values in columns A and B of that row.

 

[RedOctoberKnight]

Right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code into the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim tempA As String
    Dim tempB As String

'   See if any cells in column C manually updated
    Set rng = Intersect(Target, Columns("C:C"))
   
'   Exit if no cells in column C updated
    If rng Is Nothing Then Exit Sub
   
'   Check updated entry in column C
    For Each cell In rng
        If cell.Value = "Switch" Then
'           Swap values
            Application.ScreenUpdating = False
            tempA = Cells(cell.Row, "A")
            tempB = Cells(cell.Row, "B")
            Cells(cell.Row, "A") = tempB
            Cells(cell.Row, "B") = tempA
            Application.ScreenUpdating = True
        End If
    Next cell
   
End Sub

Now, whenever the value "Switch" is entered into column C, it will automatically flip the values in columns A and B of that row.

Thank you. I couldn't get it work at first but then i realized "Switch" is case sensitive.

Is there a way to make it so that when I delete switch from column "C", it will automatically flip back to the original values?

Thanks,

 

[Joe4]

Is there a way to make it so that when I delete switch from column "C", it will automatically flip back to the original values?

How does it know what the "original values were" (what values were in what cell)?
Excel has no memory of what was originally in what cell, unless you store those values somewhere.

If that is important, then you may want to change your whole mindset and look to adopt a solution like Dante suggested.

 

[RedOctoberKnight]

How does it know what the "original values were" (what values were in what cell)?
Excel has no memory of what was originally in what cell, unless you store those values somewhere.

If that is important, then you may want to change your whole mindset and look to adopt a solution like Dante suggested.

My apologies, "original values" isnt probably the correct term. Basically If Switch is deleted out of "c" then it would just flip the cells again which is what they would be if switch was never entered. I think i was able to figure it out by pasting the If cell.value statement at the end of the code. i then just changed "Switch" to "". I'm sure it's not the "correct" way to do it but it seems to be working.

Set rng = Intersect(Target, Columns("C:C"))
    
'   Exit if no cells in column C updated
    If rng Is Nothing Then Exit Sub
    
'   Check updated entry in column C
    For Each cell In rng
        If cell.Value = "Switch" Then
'           Swap values
            Application.ScreenUpdating = False
            tempA = Cells(cell.Row, "A")
            tempB = Cells(cell.Row, "B")
            Cells(cell.Row, "A") = tempB
            Cells(cell.Row, "B") = tempA
            Application.ScreenUpdating = True
        End If
    
    Next cell
        For Each cell In rng
        If cell.Value = "" Then
'           Swap values
            Application.ScreenUpdating = False
            tempA = Cells(cell.Row, "A")
            tempB = Cells(cell.Row, "B")
            Cells(cell.Row, "A") = tempB
            Cells(cell.Row, "B") = tempA
            Application.ScreenUpdating = True
        End If
    Next cell
End Sub

 

[Joe4]

The potential problem with the method you describe if it will also run if it changes from ANYTHING to nothing, not just from "Switch" to nothing.

By the way, if you do not case about case, and want it to run if it is "Switch", "switch", or "SWITCH", you can change this line:

        If cell.Value = "Switch" Then

to

        If UCase(cell.Value) = "SWITCH" Then

and it will run regardless of case.

 

[RedOctoberKnight]

The potential problem with the method you describe if it will also run if it changes from ANYTHING to nothing, not just from "Switch" to nothing.

By the way, if you do not case about case, and want it to run if it is "Switch", "switch", or "SWITCH", you can change this line:

        If cell.Value = "Switch" Then

to

        If UCase(cell.Value) = "SWITCH" Then

and it will run regardless of case.

Thats is a very good point. May have to rethink my tactic.

Hopefully I'm not being too needy but my I ask another question?
What if i wanted to switch the contents of two cells that are in the same column? But have it switch with a specified cell based on a cell value.

For example,

A	B	C	D
Jim	Cat	Bob	SWITCH
Dan	Dog
Bob	Bird
Greg	Cat

So basically, if I put Bob in column "C" and put "SWITCH" in column "D", it would switch the contents of column B between Jim and Bob

A	B	C	D
Jim	Bird	Bob	SWITCH
Dan	Dog
Bob	Cat
Greg	Cat

Hopefully that makes sense.

Thank you for all your help so far.