Can I swap cell info?

listerheb · Aug 22, 2003

I constantly have to swap content from a group of five cells with another group of five cells. What I have been doing is copying from the first five to a blank five, then taking the second five and copying it to the first, then moving the one I moved at the begining to the second set of five, and finally deleting the left over. Is there a way to select both sets and swap the contents? I must do this over a hundred times in a day.

Yogi Anand · Aug 22, 2003

Hi listerheb:

Welcome to MrExcel Board!

Is it the same two set of cells that you swap the values from, or are these different sts of cells that you swap at different times?

In any event, the following code will swap the values from cell A1:A5 to C1:C5 and then if you want again from c1:c5 to A1:A5 ...

Code:

Sub ySwap()
    Set temp = [d1:d5]
    [A1:A5].Copy temp
    [c1:c5].Copy [a1]
    temp.Copy [c1]
    temp.Clear
End Sub

I am using Temp (cells D1:D5) as a temporary staging area -- and if the cells D1:D5 in your case can not be spared, set the Temp to be another set of 5 cells.

I hope this helps. If I have misunderstood your question -- my apologies!

listerheb · Aug 23, 2003

It is almost never the same set twice. to further complicate things because of the format of the sheet, the cells I have to swap are not all in the same column or row. It's driving me crazy.

Smitty · Aug 23, 2003

Welcome to the Board!

A few questions:

Any chance you can utilize some named ranges? (and build from there...)

Are your repetitive tasks identical in their sequence? I.E. Copy Range A to Range B & Replace, repeat with a different range, etc.? If you can define a sequence to the process then it can be "mapped".

WHY do you have to do this? (Because if it's driving you nuts, then surely someone else will want to figure out why...)

Regardless, try posting some of your worksheet using Colo's HTML maker, which can be found at the bottom of the page. It will give the folks here much better insight into what you are trying to accomplish (and get you an answer that much quicker!)

Hope that helps,

Smitty

P.S. with Yogi on the team, you're sure to get an answer...just spell it out.

Yogi Anand · Aug 23, 2003

Hi listerheb:

So, what we are dealing with are two sets of ranges that have to be swapped, and the ranges could be different every time. if this is the case then, you may want to try ...

Code:

Sub YSwap2()
    swap1 = InputBox("key-in range for Swap1")
    Set swap1 = Range(swap1)
    swap2 = InputBox("key-in range for Swap2")
    Set swap2 = Range(swap2)
    Set temp = [d1:d5]
    swap1.Copy temp
    swap2.Copy swap1
    temp.Copy swap2
    temp.Clear
End Sub

In the following simulation ...

y030822h1.xls

A

B

C

D

E

F

G

1

a_1

2

a_2

Swap1

3

a_3

Swap2

4

a_4

Temp

5

a_5

6

7

b_1

8

b_2

9

b_3

10

b_4

11

b_5

Sheet8

The macro prompts me to key-in a range for Swap1, and Swap2, then set those ranges accordingly and then proceeds to swap the values between Swap1 and Swap2.

My macro requires selecting the ranges on prompts to Input Boxes, and use a Temp range -- based on your project constraints, and/or personal preferences, this method may or may not work for you. However, you may be able to adapt it to suit your needs/constraints. If I have misunderstood your question -- my apologies!

Smitty · Aug 23, 2003

NICE!

listerheb · Aug 23, 2003

That is so close. It's working for regular cells however some of the cells in my sheet are merged. I get a can't copy merged cell error. I will put up pics of the sheet tomorrow from home. I can't do it from work. Unless you know already how to get around that.

Yogi Anand · Aug 23, 2003

Hi listerheb:

Ah -- merged cells!

You are going to run into some problems with merged cells. In any event, the following macro will swap the values from two ranges, each 1 column wide and 5 rows deep -- one or both ranges may consist of merged cells ...

Code:

Sub YSwap3()
    
    '**** Yogi Anand on Aug-23-2003 *********************************************
    '*    swaps the values from 2 ranges 1 column wide and 5 rows deep          *
    '*    one or both ranges may consist of merged cells
    '****************************************************************************
    Beep
    swap1r = InputBox("key-in range for Swap1")
    [E1:E5] = "=Index(swap1, {1,2,3,4,5}, 1)": Set swapme1 = [E1:E5]: [E1:E5].Value = [E1:E5].Value
    Beep
    swap2r = InputBox("key-in range for Swap2")
    Cells(Range(swap1r).Row, Range(swap1r).Column) = Cells(Range(swap2r).Row, Range(swap2r).Column)
    Cells(Range(swap1r).Row + 1, Range(swap1r).Column) = Cells(Range(swap2r).Row + 1, Range(swap2r).Column)
    Cells(Range(swap1r).Row + 2, Range(swap1r).Column) = Cells(Range(swap2r).Row + 2, Range(swap2r).Column)
    Cells(Range(swap1r).Row + 3, Range(swap1r).Column) = Cells(Range(swap2r).Row + 3, Range(swap2r).Column)
    Cells(Range(swap1r).Row + 4, Range(swap1r).Column) = Cells(Range(swap2r).Row + 4, Range(swap2r).Column)
    Cells(Range(swap2r).Row, Range(swap2r).Column) = WorksheetFunction.Index([E1:E5], 1, 1)
    Cells(Range(swap2r).Row + 1, Range(swap2r).Column) = WorksheetFunction.Index([E1:E5], 2, 1)
    Cells(Range(swap2r).Row + 2, Range(swap2r).Column) = WorksheetFunction.Index([E1:E5], 3, 1)
    Cells(Range(swap2r).Row + 3, Range(swap2r).Column) = WorksheetFunction.Index([E1:E5], 4, 1)
    Cells(Range(swap2r).Row + 4, Range(swap2r).Column) = WorksheetFunction.Index([E1:E5], 5, 1)
    swapme1.Clear
    MsgBox "Done!"
End Sub

I hope this helps. If I have misunderstood your question -- my apologies!

listerheb · Aug 23, 2003

Here is part of the sheet I use. The type of change I need to make is Z7:Z9 with AH12:AH14 it could be any two sets of 5 cells. but the whole sheet looks like that. Is there a way that it will allow me to click and drag to select the cells? That would be the only way to make it faster than doing it manually.

blank football field nov 2003.xls

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

7

BJ30

BJ28

LIR27

TCP29

8

1:00PM

-

5:00PM

-

7:00PM

-

9

Joe

Mark

Kelly

10

11

12

CS26

FPG25

MB23

CS24

13

8:00PM

-

8:00PM

-

8:30PM

-

14

Sean

Jennifer

Mike

15

16

17

RLT14

PBJ12

FPG11

RLT13

18

-

19

20

21

Dealer

Smitty · Aug 23, 2003

Are you working on a time sheet?

If so, and please, no offense intended, but it looks like an awfully convoluted way to put it together. (But I'm only looking at columns Z-AN, so I'm probably totally off base with what A-Y represent).

Maybe reconfiguring your worksheet might aid the process.

Sorry for misunderstanding...

It's Saturday, HOT, and I dive into the AC every once in a while after working on the roof! (READ AS: "Brain's still fried!)

Smitty

Can I swap cell info?

listerheb

Active Member

Excel Facts

Yogi Anand

MrExcel MVP

listerheb

Active Member

Smitty

Legend

Yogi Anand

MrExcel MVP

Smitty

Legend

listerheb

Active Member

Yogi Anand

MrExcel MVP

listerheb

Active Member

Smitty

Legend

Similar threads

Forum statistics

Share this page

Can I swap cell info?

Active Member

Excel Facts

MrExcel MVP

Active Member

Legend

MrExcel MVP

Legend

Active Member

MrExcel MVP

Active Member

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock