Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.