Friday, November 22, 2013

Excel VBA macro to swap 2 values in cells

It  is very useful some times to have a shortcut to swap 2 cell values. I have created a macro in VBA to do this. Just copy paste the code below in you VBA module.

This video will help you with the procedure


Sub Swap2Values()
    Dim Value1 As Range, Value2 As Range
    Dim TempValue1 As String, TempValue2 As String
 
    If Selection.Cells.Count > 2 Or Selection.Cells.Count < 2 Then
        MsgBox "Please select only 2 cells. For other options check back soon!"
        End
    End If
   
    If Selection.Areas.Count > 1 Then
        Set Value1 = Selection.Areas(1).Cells(1, 1)
        Set Value2 = Selection.Areas(2).Cells(1, 1)
    ElseIf Selection.Rows.Count > Selection.Columns.Count Then
        Set Value1 = Selection.Range("A1")
        Set Value2 = Selection.Range("A2")
    Else
        Set Value1 = Selection.Range("A1")
        Set Value2 = Selection.Range("B1")
    End If
    TempValue1 = Value1
    TempValue2 = Value2
    Value1 = TempValue2
    Value2 = TempValue1
End Sub

5 comments:

  1. Great! But if i want to swap not only the value of the cells, but the format and the formula in thet cells? How can i doit?

    ReplyDelete
  2. Sorry to say this
    MS Excel Currency Converter Number to Text - spell number
    In this I found an error
    559854 is read as Fifty five lac fifty nine thousand eight hundred fifty four rupees
    instead of Five lac fifty nine thousand eight hundred fifty four rupees

    ReplyDelete
    Replies
    1. Hello,

      Thanks for your feedback, it really helps me to improve this tool
      I have fixed the issue in the latest version, see my latest post

      Have look at it https://www.youtube.com/watch?v=t-PE5h6rGrQ


      Delete
  3. Yes i am totally agreed with this article and i just want say that this article is very nice and very informative article.I will make sure to be reading your blog more. You made a good point but I can't help but wonder, what about the other side? !!!!!!THANKS!!!!!!
    excel vba courses london

    ReplyDelete
  4. Pretty nice post. I just stumbled upon your weblog and wanted to say that I have really enjoyed browsing your blog posts. After all I’ll be subscribing to your feed and I hope you write again soon!
    excel courses

    ReplyDelete