Find, Select and Copy

[Steven123]

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
Thanks in advance!

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

 

[Jim North]

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
firstAddress = C.Address
Do
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With

</pre>

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

 

[Steven123]

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

 

[Jim North]

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
firstAddress = C.Address
Do
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With

' display an error message if the meter is not found
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>

 

[Steven123]

Yep... that did the trick


Thanks!!!!!