Macro help to resolve search a worksheet for match in another

[andycapp28]

I have this code but cannot get it to run into my loop.
Any help would be most welcome.

Requirement is to read all column C in worksheet1 and for each cell check its value for existence in worksheet2. If found then I need to copy the value from worksheet2 activecell -1 into activecell -1 of worksheet1.

Sub GetImportID()
    
 Dim rLookfor
 Dim rLookin
    
    Set rLookfor = Worksheets("Sheet1").Range("B1").End(xlDown)
    Set rLookin = Worksheets("Sheet2").Range("C1").End(xlDown)
    
Do Until ActiveCell.Value = ""
    
    For Each c In rLookfor.Cells
        
        f = rLookin.Find(c.Value)
        
        If ActiveCell.Value = f Then
        
        rLookin(ActiveCell, ActiveCell.Offset(0, -1)).Copy
        
        rLookfor(ActiveCell, ActiveCell.Offset(0, -1)).Paste

        End If
    Next
    
        rLookfor(ActiveCell, ActiveCell.Offset(1, 0)).Select
    
Loop
    
End Sub

 

[SkipVought]

Hi,

Sub GetImportID()
    Dim r As Range, c As Range
    Dim rLookfor As Range
    Dim rLookin As Range
    
    With Worksheets("Sheet1")
        Set rLookfor = .Range(.[B1], .[B1].End(xlDown))
    End With
    
    With Worksheets("Sheet2")
        Set rLookin = .Range(.[C1], .[C1].End(xlDown))
    End With
    
'It is not very good programming to use activecell like this
'how can you be sure it's in the right place?
'how do you expect someone else looking at your code to know where it is?

    For Each c In rLookfor
        Set r = rLookin.Find(c.Value)
        
        If Not r Is Nothing Then
            r.Offset(0, -1).Copy
            
            c.Offset(0, -1).PasteSpecial xlPasteAll
            
        End If
    Next

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Skip, Hi again

Fantastic response, it works and has eased my development time immensely.

I totally agree with your comments about my programming.

Trying to learn VBA via Help and Forums is not ideal but the only method I can afford.

Many Thanks

AC

 

[SkipVought]

Don't know if there is more to this excersize, but this could simply be accompished on the sheet with lookup functions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

If you feel like spending the time to advise me of how I would use lookup that would be brilliant. I'm sure the knowledge I would gain will be used many times over.

Thanks

 

[SkipVought]

If I understand the situation, you have a column a values in Sheet1 column B and you want to populate the corresponding row in column A with the data from Sheet2.

Correct?

In Sheet1
[tt]
A1: =INDEX(Sheet2!A:A,MATCH(B1,Sheet2!B:B,0),1)
[/tt]
Look at help on MATCH and INDEX.

Post back if you have further questions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Thanks Skip,

I can follow the logic of the index and match example you supplied.

I cannot though get it to deliver the answer compared to the GetImportID code.

It just leaves a #N/A result

 

[SkipVought]

#N/A means that the lookup value cannot be found in the lookup range.

It worked for me with your data.

Please post YOUR formula.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Maybe a better idea is for me to attach a sample of data.

Having said that I have been unable to complete step 3 attachment for some reason.

so here is a pasted version.

Sheet 2
A B C
KeyInd ImportID ConsID
O 03405-079-0085038 102972
O 24/03/92,0021709 11565
O 24/03/92,0021982 11653
O 24/03/92,0022152 11711
O 24/03/92,0022160 11713
O 24/03/92,0022228 11725
O 24/11/94,0022063 25549
O 24/03/92,0022000 11658


Sheet 1
A B C
11565 Twist
11565 Higgins
11565 Abbott
11653 Kim
11653 Lee
11653 Jo
11711 Earth
11713 Wind
11725 Fire
25549 Water
25549 Petrol
25549 Paper
25549 Cloth
102972 Car
102972 Bus
102972 Tram


Formula I transposed from yours was as follows;
=INDEX(Sheet2!C:C,MATCH(B2,Sheet2!C:C,0),1)
or
=INDEX(Sheet2!C:C,MATCH(Sheet1!B2,Sheet2!C:C,0),1)

Cheers
AC

 

[PHV]

A1: =INDEX(Sheet2!B:B,MATCH(B1,Sheet2!C:C,0),1
Then copy down

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo.
A1: =INDEX(Sheet2!B:B,MATCH(B1,Sheet2!C:C,0),1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[andycapp28]

Thanks.

I still had to tweak it somewhat but I now have the results expected.

=INDEX(Sheet2!C:C,MATCH(B2,Sheet2!C:C,0),1))

AC

 

[SkipVought]

[tt]
=INDEX(Sheet2!C:C,MATCH(B2,Sheet2!C:C,0),1))
[/tt]
does not make sense at all.

You are using the value in B2 to lookup in sheet2!C:C, and then returning the SAME VALUE!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Yes of course it does, I must be more careful with the things I say as responses. I didn't check the results properly as I was not comparing them to my macro version.

=INDEX(Sheet2!B:B,MATCH(B2,Sheet2!C:C,0),1)

Thanks Skip

AC

 

[andycapp28]

Hi

I need to change my code on the range find as I must find a wholematch in c.value

Set r = rLookin.Find(c.Value, LookAt:=xlWhole)

Need help to overcome this error
:
unable to get the find property of the range class

Thanks hoping for assistance
AC

 

[SkipVought]

How in rLookin declared and assigned? Please post your code

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Hi Skip

You helped me with this sometime back

Sub GetImportID()

' Requirement is to speed up the importing of Individual Relationship information _
  held in excel file (Sheet 1)
  
' Sheet 1 column B is read until empty, the Constituent ID from column B _
  is used to search column C in Sheet2
  
' Sheet 2 contains the Import_Id for the Constituent ID.
  
' If the Constituent_ID is found in Sheet 2 the Import_ID from column B _
    is passed back to Sheet 1 and placed in column A.
    
    Dim r As Range, c As Range
    Dim rLookfor As Range
    Dim rLookin As Range
    Dim xlWhole As Boolean
    
    With Worksheets("Sheet1")
        Set rLookfor = .Range(.[B1], .[B1].End(xlDown))
    End With
    
    With Worksheets("Sheet2")
        Set rLookin = .Range(.[C1], .[C1].End(xlDown))
    End With
    
    For Each c In rLookfor
        Set r = rLookin.Find(c.Value, LookAt:=xlWhole)
        
        If Not r Is Nothing Then
            r.Offset(0, -1).Copy
            
            c.Offset(0, -1).PasteSpecial xlPasteAll
            
        End If
    Next

End Sub

 

[SkipVought]

So what is the RANGE that rLookin is set to? Put a BREAK in your code just after the range is SET and observe rLookin.address in the Watch Window.

faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[andycapp28]

Thanks

Added Watch but see no r.Lookin.address

 

[SkipVought]

You have to ADD it to you watch. did you read the FAQ and VB Help?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}