Swap selected cell values

I am looking for a simple macro to swap the contents of two selected
cells.  The cells might not be next to each other.

I know what I want it to do, but I don't know vba well enough.

If anyone has a moment to help, here is what I want to do.

Select cell1, could be A1
Control-Click additional selection of cell2, could be C1

Hot-Key Macro to:
Assign cell1 value to variable1
Assign cell2 value to variable2
Assign variable1 value to cell2
Assign variable2 value to cell1

Note: the specific cells being selected will not always be the same.

So after the macro is run the values that started in cells A1 and C1
would swap with each other.

Any suggestions would be appreciated.

TK


0
tdk1138 (3)
11/28/2008 8:52:03 PM
excel 39879 articles. 2 followers. Follow

5 Replies
668 Views

Similar Articles

[PageSpeed] 35

Sub swap_um()
Dim v0 As Variant
Dim v1 As Variant
Dim s(2) As String
If Selection.Count <> 2 Then Exit Sub
i = 0
For Each rr In Selection
    s(i) = rr.Address
    i = i + 1
Next
v0 = Range(s(0)).Value
v1 = Range(s(1)).Value
Range(s(1)).Value = v0
Range(s(0)).Value = v1
End Sub
-- 
Gary''s Student - gsnu2007k


"Cortez" wrote:

> I am looking for a simple macro to swap the contents of two selected
> cells.  The cells might not be next to each other.
> 
> I know what I want it to do, but I don't know vba well enough.
> 
> If anyone has a moment to help, here is what I want to do.
> 
> Select cell1, could be A1
> Control-Click additional selection of cell2, could be C1
> 
> Hot-Key Macro to:
> Assign cell1 value to variable1
> Assign cell2 value to variable2
> Assign variable1 value to cell2
> Assign variable2 value to cell1
> 
> Note: the specific cells being selected will not always be the same.
> 
> So after the macro is run the values that started in cells A1 and C1
> would swap with each other.
> 
> Any suggestions would be appreciated.
> 
> TK
> 
> 
> 
0
GarysStudent (1572)
11/28/2008 9:09:01 PM
Hi,

Try this

Sub Swap()
    Y = Selection.Areas(1)
    Selection.Areas(1) = Selection.Areas(2)
    Selection.Areas(2) = Y
End Sub

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Cortez" wrote:

> I am looking for a simple macro to swap the contents of two selected
> cells.  The cells might not be next to each other.
> 
> I know what I want it to do, but I don't know vba well enough.
> 
> If anyone has a moment to help, here is what I want to do.
> 
> Select cell1, could be A1
> Control-Click additional selection of cell2, could be C1
> 
> Hot-Key Macro to:
> Assign cell1 value to variable1
> Assign cell2 value to variable2
> Assign variable1 value to cell2
> Assign variable2 value to cell1
> 
> Note: the specific cells being selected will not always be the same.
> 
> So after the macro is run the values that started in cells A1 and C1
> would swap with each other.
> 
> Any suggestions would be appreciated.
> 
> TK
> 
> 
> 
0
11/28/2008 9:24:10 PM
Excellent, thank you very much!

TK

On Nov 28, 3:09=A0pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> Sub swap_um()
> Dim v0 As Variant
> Dim v1 As Variant
> Dim s(2) As String
> If Selection.Count <> 2 Then Exit Sub
> i =3D 0
> For Each rr In Selection
> =A0 =A0 s(i) =3D rr.Address
> =A0 =A0 i =3D i + 1
> Next
> v0 =3D Range(s(0)).Value
> v1 =3D Range(s(1)).Value
> Range(s(1)).Value =3D v0
> Range(s(0)).Value =3D v1
> End Sub
> --
> Gary''s Student - gsnu2007k
>
>
>
> "Cortez" wrote:
> > I am looking for a simple macro to swap the contents of two selected
> > cells. =A0The cells might not be next to each other.
>
> > I know what I want it to do, but I don't know vba well enough.
>
> > If anyone has a moment to help, here is what I want to do.
>
> > Select cell1, could be A1
> > Control-Click additional selection of cell2, could be C1
>
> > Hot-Key Macro to:
> > Assign cell1 value to variable1
> > Assign cell2 value to variable2
> > Assign variable1 value to cell2
> > Assign variable2 value to cell1
>
> > Note: the specific cells being selected will not always be the same.
>
> > So after the macro is run the values that started in cells A1 and C1
> > would swap with each other.
>
> > Any suggestions would be appreciated.
>
> > TK- Hide quoted text -
>
> - Show quoted text -

0
tdk1138 (3)
11/28/2008 9:25:51 PM
That also works for my stated need.  It errors out if the selection
was created as a range of two cells, but otherwise is very simple.
Thank you.

On Nov 28, 3:24=A0pm, Shane Devenshire
<ShaneDevensh...@discussions.microsoft.com> wrote:
> Hi,
>
> Try this
>
> Sub Swap()
> =A0 =A0 Y =3D Selection.Areas(1)
> =A0 =A0 Selection.Areas(1) =3D Selection.Areas(2)
> =A0 =A0 Selection.Areas(2) =3D Y
> End Sub
>
> If this helps, please click the Yes button
>
> Cheers,
> Shane Devenshire
>
>
>
> "Cortez" wrote:
> > I am looking for a simple macro to swap the contents of two selected
> > cells. =A0The cells might not be next to each other.
>
> > I know what I want it to do, but I don't know vba well enough.
>
> > If anyone has a moment to help, here is what I want to do.
>
> > Select cell1, could be A1
> > Control-Click additional selection of cell2, could be C1
>
> > Hot-Key Macro to:
> > Assign cell1 value to variable1
> > Assign cell2 value to variable2
> > Assign variable1 value to cell2
> > Assign variable2 value to cell1
>
> > Note: the specific cells being selected will not always be the same.
>
> > So after the macro is run the values that started in cells A1 and C1
> > would swap with each other.
>
> > Any suggestions would be appreciated.
>
> > TK- Hide quoted text -
>
> - Show quoted text -

0
tdk1138 (3)
11/28/2008 9:36:08 PM
Hi,

Glad to help. If you are interested in multiple cells in each range, will 
they always be in a column, a row, or any rectangle?

Cheers,
Shane Devenshire

"Cortez" wrote:

> That also works for my stated need.  It errors out if the selection
> was created as a range of two cells, but otherwise is very simple.
> Thank you.
> 
> On Nov 28, 3:24 pm, Shane Devenshire
> <ShaneDevensh...@discussions.microsoft.com> wrote:
> > Hi,
> >
> > Try this
> >
> > Sub Swap()
> >     Y = Selection.Areas(1)
> >     Selection.Areas(1) = Selection.Areas(2)
> >     Selection.Areas(2) = Y
> > End Sub
> >
> > If this helps, please click the Yes button
> >
> > Cheers,
> > Shane Devenshire
> >
> >
> >
> > "Cortez" wrote:
> > > I am looking for a simple macro to swap the contents of two selected
> > > cells.  The cells might not be next to each other.
> >
> > > I know what I want it to do, but I don't know vba well enough.
> >
> > > If anyone has a moment to help, here is what I want to do.
> >
> > > Select cell1, could be A1
> > > Control-Click additional selection of cell2, could be C1
> >
> > > Hot-Key Macro to:
> > > Assign cell1 value to variable1
> > > Assign cell2 value to variable2
> > > Assign variable1 value to cell2
> > > Assign variable2 value to cell1
> >
> > > Note: the specific cells being selected will not always be the same.
> >
> > > So after the macro is run the values that started in cells A1 and C1
> > > would swap with each other.
> >
> > > Any suggestions would be appreciated.
> >
> > > TK- Hide quoted text -
> >
> > - Show quoted text -
> 
> 
0
11/28/2008 11:28:02 PM
Reply:

Similar Artilces:

Way do do search/replace on beginning/end of cell w/o functions?
Hi, I've been writing various functions to search/replace beginning/ends of cells, however wondering if there is a way to do the simpler ones with just Search/Replace function. In word I'd just do ^pString orString^p but not sure if there is a symbol I can use to mean beginning/end of cell. For instance I want to replace all Cells with Board Certified but not where * Board Certified I can of course write a function but the sheet is unwieldy as it is and it is a simple 'request'... hi, ! for a begin/end cells replacement, try first with a cust...

Selecting part of a filed
I have table with a field like this one and I need to separate the dollar amount out, the amoount can range from 3 digit to 6 digit or more. XY4567 Converter belt $3.40 or 7890 Transmission $1250.00, I need to develope a query to extract the dollar amounts or cost Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1 val( Mid(fieldName, 1+InStr(fieldName & "$" , "$"))) Hoping it may help, Vanderghast, Access MVP "CharlesCount via AccessMonster.com" <u35616@uwe> wrote in message...

zeroing down stock value
does anyone know how to zero the stock value, leaving all sales history and or information intact. Anyone using RR multi location Let me know and email me what question you have on RR Multi Location. Afshin Alikhani - afshin@retailrealm.co.uk Retail Realm = = = = = = = = = = = = = "Andaiye" <Andaiye@discussions.microsoft.com> wrote in message news:11CB28F8-9E71-491D-AF0F-0E94359AC19A@microsoft.com... > does anyone know how to zero the stock value, leaving all sales history > and > or information intact. > > Anyone using RR multi location If you want to z...

How to Update Selections for (Numerous) Validation Lists
Dear Excel Experts: I have many validation lists which source can change frequently. How do I automatically update the validation lists every time the sources change? I got some lead from www.contextures.com (see below). But, it only helps when I have one validation list. Meanwhile, I have many lists. DV0022 - Update Validation Selections -- If you change an item in a data validation source list, the worksheet may show previously selected items. Event code can update the worksheet when you update the source list. DataValUpdate.zip 10kb Updated 07-Sep-11 Thanks in advanc...

deleting random amounts of data from user selected columns..
I need help! I cannot seem to figure out the best way to modify a worksheet in excel. I have a worksheet with 38 col's, and 47 rows of data. I need to allow the user to select one or more columns, then remove a random number cells from the selected columns. Any ideas???? ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** Hi Jimmy This should get you started Sub RandomRemove() Dim iNumber As Integer, iLoop As Integer Dim lRow As Long, iCol As Integer iNumber = Int((Selection.Columns.Count * Rnd) + 1) ...

Insert string into a Cell ?
Hi, How do I insert string into a cell ? Dim myString As string myString = "abcd" shp.CellsSRC(visSectionProp, visRowProp, visCustPropsValue) = myString 'This will yield with type mismatch error. Thanks. Hello m, You've got a reference to the cell but not the property of the cell object. So if you want to set the cell's formula you need to use that property. Also, as you're adding a string you need to wrap it up in quotes (note two double quotes in a row get evaluated as a single set of quotes). Have a go with the following: shp.CellsSRC(visSectionProp, ...

DPM2010RC, Does selecting Bare Metal Recovery cover everything I n
Or do I need to also select System State and/or the Volumes on the system? What all do I need to be able to recreate a system from scratch and have it exactly like it was before? I would also like to add to this. What Operating Systems does BMR support? Client OS such as XP,Vista, and 7? "Daniel Segel" wrote: > Or do I need to also select System State and/or the Volumes on the system? > What all do I need to be able to recreate a system from scratch and have it > exactly like it was before? > > BMR is supported only for WIN2K8 and WIN2K8 R...

Selecting a Drawing Object
I need to get the name of a drawing object when I click it via a left click, something like: Sub TheDrawingObject msgbox(me.name) End Sub I need to assign it to a variable. Any help would be greatly appreciated. Option Explicit Sub TheDrawingObject() Dim myShape as shape set myshape = activesheet.shapes(application.caller) 'just to show a few ways to get it and other options msgbox application.caller _ & vblf & myshape.name _ & vblf & myshape.topleftcell.address End sub Troubled User wrote: > > I n...

I want to bold selected text, entire document is bolded?
I'm working in Word. When I highlight a section of text to be bolded, italicized, etc. the entire document gets bolded italicized, etc. How do I stop that from happening? Might try a Word group... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Seeker" <Seeker@discussions.microsoft.com> wrote in message news:A39826B2-75E3-4C9E-ACBC-9DD230741F28@microsoft.com... > I'm working in Word. When I highlight a section of text to be bolded, > italicized, etc. the entire document gets bolded italicized, etc. ...

Return to cell whose address is stored in different cell
Hi.. Thanks for your help! I am writing a macro in Visual Basic. I need to Select a Range from first Cell within the Range, copy the Range, paste the Range to a location with formulas so I can edit it, then copy the edited version of the Range and paste it back to it's orgional location (probably by using the first Cell selected). I've tried previous property, source range and afew other things that I couldn't make work. Got any Ideas???? -- spydor ------------------------------------------------------------------------ spydor's Profile: http://www.excelforum.com/member...

swap axes
In a line chart, Excel seems to predetermine the category axis - how can I swap this so that my Y-value axis appears along the bottom? Hi, Have a look at Jon Peltier's page http://peltiertech.com/Excel/Charts/Y_CategoryAxis.html Cheers Andy Fridgemaster wrote: > In a line chart, Excel seems to predetermine the category axis - how can I > swap this so that my Y-value axis appears along the bottom? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Hi, I've got the same problem as Fridgemaster. Andy, good try, but your solution is not what we are asking. Prima...

controlling active cell
I have a spreadsheet with 4 columns. I am scanning some bar codes into the 2nd and the 3rd column. I want to the active cell to change to the 3rd cell after I scan the bar code for the second column. This is obviously easy to do. But I want it go to the 2nd column of the next line after I scan the bar code for the 3rd column. Is there any way to do this? Step 1 Description Tag # Serial # Pallet x Step 2 Description Tag # Serial # Pallet x x Step 3 Description...

blank cells
my table has columns with formulas. some of the rows are blank. the formulas do not apply to the blank rows. in some of the colums, in blank cells a zero prints and in other columns a $ sign prints in empty cells , and others get "#value!" while some remain empty. my question is this - if there is no data, I would like the cells to remain blank. how do I accomplish this? "rudy" <anonymous@discussions.microsoft.com> a �crit dans le message de news: c56e01c47a56$72c5ac70$a301280a@phx.gbl... > my table has columns with formulas. some of the rows are >...

Swapping cells on linked spreadsheets
I have 2 spreadasheets. The fist one is named "Name and Bed Entry". On this spreadsheet the user literally types in a name as it relates to a bed (or room number) and other info. The name and room number is then linked to another spreadsheet called Master Resident Info. This spreadsheet has the cost of that room. So, in Master Resident Info, the name entry actually looks like ... =IF('[Name & Bed Entry test.xls]Name Entry'!$B$52="","",'[Name & Bed Entry test.xls]Name Entry'!$B$52). The cost of the room in Master Resident...

Hlookup with Merged cell as look up value
Hopefully someone can help me out on this one. I have a spreadsheet that looks similar to this: 2/15/2010 3/15/2010 . . . Site# INV VALUE INV VALUE 1 34 332 40 440 2 47 250 47 250 3 55 476 36 420 Total 136 1058 123 1100 I am trying to use a Hlookup to return both the Totals for both the INV and Value in a compainon sheet. The date cells are merged over the INV and VAL Colums so my Hlookup fo...

Comparing Repeating Cells
How do you express a formula for the following examples If A2, B2 and C2 all cells with same numbers as A1, B1 and C1, in any order, then D1 equals 3, (ie 3,,7,,2 followed by 3,,2,,7) but If A2, B2 and C2 have only 2 cells which is the same as A1, B1 and C1, in any order, then D1 equals 2, (ie 3,,7,,2 followed by 2,,3,,4) or (ie 3,,7,,2 followed by 3,,7,,7) but If A2, B2 and C2 have only 1 cell which is the same as A1, B1 and C1, in any order, then D1 equals 1, (ie 3,,7,,2 followed by 0,,2,,4) or (ie 3,,7,,2 followed by 3,,3,,8) or ie 3,,7,,2 followed by 2,,2,,2 but If A2, B2 and C2 have n...

Protecting formulas in cells
I need to know how to protect only the cells that have a formula in them, and still allow editing of other cells. How. You can select special ranges by: ctrl-A to select the whole sheet Edit|goto|Special click on Formulas (or constants or blanks) Now you've just selected all the formulas (or constants or blanks). Format|Cells|Protection Tab Check Locked or uncheck Locked (depending on what you selected) Now protect the worksheet. Tools|Protection|protect sheet. ==== Depending on how many cells I want different, I'll select everything and unlock them. Then come back and lock ju...

SQL Select Problem
Hi all I have a little problem with a select. I would need to have the most simple solution :-) I have the following table: [FK_Candidate], [Key], [Value] 1 'Profession' 'Informatiker' 2 'Profession' 'Informatiker' 1 'Source' 'Quelle1' 2 'Source' 'Quelle2' I need to get all candidates with Profession = Informatiker AND Source =...

strange cell format
Everytime when I create a new excel file, all cells are divided by 10. when I type in a 1 then the cell's value is 0.1. what is even worse: every formula is reduced to a zer! please help me out here! umbro Click Tools > Options > Edit tab Uncheck "Fixed Decimals" > OK "umbro" <anonymous@discussions.microsoft.com> wrote in message news:055201c3963b$43145a30$a401280a@phx.gbl... > Everytime when I create a new excel file, all cells are > divided by 10. when I type in a 1 then the cell's value is > 0.1. > what is even worse: every formul...

assign a wav sound to a cell value
how do I assign a wav sound to a cell value? I have a column with OR statements. when a cell in that column turns TRUE, I want to hear a wav sound. how can I do this? ...

Selection within a named range
Dear all, There is a named range "Rng" in my workbook. I want to write a VBA program to select the first 10 rows of the range "Rng". What code should I write? Best Regards, Andy I don't know how you could do this, but you could create another named range consisting of the area you want. -- Ian -- "Andy Chan" <chankhandy-msnewsgroup@yahoo.com.hk> wrote in message news:43e4715e@127.0.0.1... > Dear all, > > There is a named range "Rng" in my workbook. I want to write a VBA > program to select the first 10 rows of th...

Changing the order of a name in various cells
I have a column with over 400 rows of names that most contain names reading last name first then a comma then a persons first name. Such as Sullivan, Mike. How can I change the order to read first name then last name without the comma? Such as Mike Sullivan. Thank you for looking. -- Cheers, John Cannon Hi With entry in A1: =RIGHT(A1,LEN(A1)-FIND(",",A1)-1) & " " & LEFT(A1,FIND(",",A1)-1) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "happydude" <happydude@discussions.microsoft.com> wrote in message news:9...

Excel should allow you to swap rows. Also swap columns
This is a general feature request for Excel. It would be great if I could select 2 rows (using ctrl + click). Right-click on one of those rows and have a menu choice called Swap. Example: assume that I have selected row 2 and row 4, Before: 1 2 3 4 After: 1 4 3 2 ...

How to select multi-user ???
For example, peter & john's email account are installed in the same post office or identity(I don't know how to say in outlook), when I compose new email, I can select the sender is peter or john in outlook express, but in outlook, no selection can be provided. HOW TO DO IT ??? Appreciate for any advice !!! jj <jj@discussions.microsoft.com> wrote: > For example, peter & john's email account are installed in the same > post office or identity(I don't know how to say in outlook), when I > compose new email, I can select the sender is peter or john in...

change info in 1 cell, prompts you for another
I would like to have a promt or some type of notice that when a user changes the information in cell B15 that it might be necessary to change the information in B25. Ideally, I would want a popup box/notice that says "You've changed the information within, would you like to change XX as well?" Realistically, I would settle for a simple change in cell colour or something similar to indicate that more changes may be needed. Does anyone have any ideas? Thanks! Hi, it's very simple, you should select the cell that you want to hav the pop up, then go to Data >> Vali...