# Find, Select and Copy

#### Steven123

##### New Member
Hi people!

OKay... here's my problem:

I have 8 colomns.
Colomns A and B are empty, Colomns C and D all list countries, E and F are empty and G and H contain city names. Something like this:

A:
B:
C: US
D: UK
E:
F:
G: NYC
H: LONDON

(Vertical is of course horizontal)

Sheet 2 contains a list of locations in colomn A.

I need to make a macro that will take the location in colomn A (sheet 2) and look if the location is in colomn G OR H (sheet 1).

Now here's were is becomes tricky..

If location is found in colomn G (and not H) then copy colomn C of that row to colomn A / copy colomn D of that row to colomn B / copy G to E and copy H to F.

A: US
B: UK
C: (US)
D: (UK)
E: NYC
F: LONDON
G: (NYC)
H: (LONDON)

If however locataion is found in colomn H (and not G) then copy colomn C of that row to colomn B / Copy colomn D of that row to colomn A / G to F and copy H to E.
(So not B and A have switched around and so have E and F)

A: UK
B: US
C: (US)
D: (UK)
E: LONDN
F: NYC
G: (NYC)
H: (LONDON)

Any help would be appreciated

Steven.
This message was edited by Steven123 on 2002-08-26 11:07

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hello Steven123,

I looks to me like you would be best reading thru your Sheet1 and testing Sheet2 Column A for a match. Below is a sample find:

<pre>

With Range("C:C")
Set C = .Find(Mname, LookIn:=xlValues)
If Not C Is Nothing Then
Do
End If
End With

</pre>

It is searching for the field "Mname" in column C... hope this gets you started.

Thanks for your help so far... I have built a macro to do some of the work however there are some problems.
1) If SHEET 1, FIRST row, colomn L does not contain a value that is in SHEET2 colomn A (the value to search for) then the value is still treated as a MATCH.
2) IF SHEET 1, FIRST row , colomn P does not contain a value that is in SHEET2 colomn A the macro hangs and does not go to the next row?!?

Anyone know how to solve this:

DATA:

A:
B:
C: FROM COUNTRY
D: FROM DISTRICT IN "FROM COUNTRY"
E:
F:
G: TO COUNTRY
H: TO DISTRICT IN "TO COUNTRY"
I:
J:
K: FROM LOCATION IN "FROM COUNTRY"
L: ABREVIATION OF THAT LOCATION
M:
N:
O: TO LOCATION in "TO COUNTRY"
P: ABBREVIATION OF THAT LOCATION

SHEET2: (WITH ITEMS TO SEARCH)
A: ABBREVIATION OF LOCATION

Sub AlphaBravo()

Dim myrng As Range, myrng2 As Range, cl As Range, z As Range
Set myrng = Sheets(1).Range("L1:" & [L65536].End(xlUp).Address)
Set myrng2 = Sheets(1).Range("P1:" & [P65536].End(xlUp).Address)

On Error GoTo Errorhandler

Worksheets("Data").Activate

For Each cl In myrng
If IsEmpty(cl) Then GoTo 1
Set z = Sheets(2).[a:a].Find(cl.Value, LookIn:=xlValues)
If Not z Is Nothing Then cl.Activate

ActiveCell.Offset(0, -11).Select
ActiveCell.Offset(0, 2).Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 6).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 6).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 6).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
1: Next

MsgBox "Pause..."

For Each cl In myrng2
If IsEmpty(cl) Then GoTo 2
Set z = Sheets(2).[a:a].Find(cl.Value, LookIn:=xlValues)
If Not z Is Nothing Then cl.Activate

ActiveCell.Offset(0, -1).Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -6).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -6).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 11).Range("A1").Select
Application.CutCopyMode = False
2: Next

MsgBox "Switching of locations has finished"

Exit Sub
Errorhandler:
Message = MsgBox("No Match Found")
End Sub
This message was edited by Steven123 on 2002-08-29 07:31

You dropped part of the code... the DO/LOOP is important. Here is a "fuller" example of how it works...

<pre>

' find the meter in column C
With Range("C:C")
Set C = .Find(MName, LookIn:=xlValues)
If Not C Is Nothing Then
Do
End If
End With

If C Is Nothing Then
MsgBox ("the meter number " & MName & " was not found")
Else ' display the meter requested
' move the window to the line with the meter on it
Range("G" & C.Row).Value = MCF
End If

</pre>

Yep... that did the trick

Thanks!!!!!

Replies
8
Views
293
Replies
2
Views
122
Replies
5
Views
369
Replies
7
Views
256
Replies
4
Views
184

1,219,792
Messages
6,150,291
Members
450,949
Latest member
faizanmalik10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back