?
Solved

Macro revision

Posted on 2012-08-20
2
Medium Priority
?
383 Views
Last Modified: 2012-08-20
Sub Switch()
I created a macro called Switch to switch two numbers located in adjoining cells (one above the other).  My problem is that I want the switch to occur based on whichever cell I have highlighted so that it switches the number in that cell with the number in the cell one row below it.  Instead the macro only switches whatever data is in the cells originally used when I created the macro.  

'
' Switch Macro
' Switches two prices
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Selection.Copy
    Range("D37").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("D36:D37").Select
    Selection.Copy
    Range("D35").Select
    ActiveSheet.Paste
    Range("D37").Select
    Selection.ClearContents
End Sub


Can someone please show me how to modify the macro so that it works based on the highlighted cell and not a fixed cell?  Thanks!
0
Comment
Question by:pwflexner
2 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 680 total points
ID: 38312732
Like this?

' Switch Macro
' Switches two prices
'
' Keyboard Shortcut: Ctrl+Shift+S
dim vValue as variant

vvalue=selection.value
selection.value=selection.offset(1)
selection.offset(1).value=vvalue
End Sub

Open in new window

0
 

Author Closing Comment

by:pwflexner
ID: 38312951
Perfect!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question