Move data between cells based on value of another cell

**brian0612** · 03-30-2009, 10:41 AM

I have a spreadsheet with columns z & aa that have numeric data. I would like to switch the data between the 2 cells if column ai has a value of "s". Can anyone help?

**NBVC** · 03-30-2009, 10:48 AM

If it's going to be a "one-time" thing and you just need to do the switches in one shot only...then try this...

Assuming your data has headers...

in a new column, row 2 add formula =IF(AI2="s",AA2,Z2)

and in another adjacent new column, row 2 =IF(AI2="s",Z2,AA2)

where data starts in row 2.

Then copy these down...

Then copy the 2 new columns and go to Z1.

Go to Edit|Paste Special and select Values.

Click Ok.

Now you can delete those 2 new columns added.

**ColinKJ** · 03-30-2009, 10:54 AM

Hi

You could try this

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 35 And Cells(Target.Row, 35) = "s" Then
a = Cells(Target.Row, 26): Cells(Target.Row, 26) = Cells(Target.Row, 27): Cells(Target.Row, 27) = a
End If
End Sub

Code:

Regards

Colin

**NBVC** · 03-30-2009, 10:59 AM

ColinKJ,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here