Look Up/Find using Macro

[AliStott]

Hi,

I am trying to imitate the excel LookUp function in a macro. I need to look up a contract code from one workbook in another to find the currency and rate for that code.

Using the VB Help files I wrote the following code which works, other than it doesn't give an EXACT match. For example, if the contract code is "BP", the marco will also find "FBP".

I don't think it helps that I didn't fully grasp the explanation on the help file.

Any suggestions greatly appreciated.

Many Thanks
Ali

Sub Lookup()

Set ExRateWkBk = ActiveWorkbook
ContractCode = "BP"

    With ExRateWkBk.Sheets("Transtrend").Range("A1:A50")
        Set CCode = .Find(ContractCode, LookIn:=xlValues)
        
        If Not CCode Is Nothing Then
            FirstAddress = CCode.Address
            Do
                Range(FirstAddress).Select
                ExRateCurr = ActiveCell.Offset(0, 3).Value
                ExRate = ActiveCell.Offset(0, 4).Value
                
                Set CCode = .FindNext(CCode)
            Loop While Not CCode Is Nothing And CCode.Address <> FirstAddress
        
        End If
    End With
    
    

End Sub

 

[Jon von der Heyden]

I think you need to change:

Set CCode = .Find(ContractCode, LookIn:=xlValues)

to:

Set CCode = .Find(ContractCode, LookIn:=xlValues, LookAt:=xlWhole)

HTH

 

[AliStott]

Brilliant! It worked! Thank you very much.