Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to swap the content between two cells in Excel based on the content of a third cell. So, I have a column in Excel - say Column C. I want to swap the values of the corresponding cells in Columns A and B based on whether there is an "S" in the corresponding cell in Column C. I am no expert in VB, but I have these two macros that I am trying to re-work. The first one is in the sheet module and is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("C1"), Target) Is Nothing Then
        If IsNumeric(Target.Value) And Target.Value = "S" Then
            Call swap
        End If
    End If
End Sub

The second is in the Standard module and is as follows:

Sub swap()
 Dim cellVal
 cellVal = ActiveCell.Value
 ActiveCell.Value = ActiveCell.Offset(0, 1).Value
 ActiveCell.Offset(0, 1).Value = cellVal
End Sub

It's not working for some reason. Can anyone help me out? I think it's because I have ".Count" in the first macro and I'm looking for an "S", but I dunno what to change it to. Any help would be much appreciated.

share|improve this question

Well I am not sure why you have the Target.Cells.Count check, but I am leaving it in if you require it.

The main issue I saw in your code was that you are checking your column C value for whether it is a numeric value, but you want a string. So your IsNumeric(Target.Value) code was always returning false when you enter "S in column C. So I have taken that out.

The code below will work for the entire column C:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
        Exit Sub
    End If

    If Not Intersect(Range("$C:$C"), Target) Is Nothing Then
        If Target.Value = "S" Then
            swap Target
        End If
    End If
End Sub

Sub swap(rng As Range)
    Dim cellVal As String
    cellVal = rng.Offset(0, -2).Value
    rng.Offset(0, -2).Value = rng.Offset(0, -1).Value
    rng.Offset(0, -1).Value = cellVal
End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .