Lookups in Vba

[Noz2k]

So I have a code set up which emails the data in a worksheet ("Sheet2"). However I need to set up the .to address so that it is dependant on different values on the sheet. ("J7") & ("J9").

I can hard code this fine.

Dim Recipient As String
If Range("J7") = "VALUE1" Then
    Recipient = "Email1"
Else
     If Range("J7") = "VALUE2" Then
         Recipient = "Email2"
...etc

However I would prefer to use a lookup from the sheet ("LookupLists"), so something like

If Sheets("Sheet2").Range("J7").Value = Sheets("LookupLists").Range("A20").Value Then
Recipient = Sheets("LookupLists").Range("B20").Value

But for the code to look up the value in the Named range and then use the row number to reference the cell in the Email Named Range.

Any help would be appreciated, thanks

 

[AlphaFrog]

Try something like this...

    Dim rngFound As Range
    
    With Sheets("LookupLists")
        Set rngFound = .Range("A:A").Find(Sheets("Sheet2").Range("J7").Value, , xlValues, xlWhole, xlNext, False)
    End With
    
    If Not rngFound Is Nothing Then
        Recipient = rngFound.Offset(, 1)
    Else
        MsgBox ("No match found.")
    End If

 

[Noz2k]

Thanks,

it doesn't seem to work though.

It's not recognising a recipient, nor is it generating a msgbox.

 

[AlphaFrog]

What is in Sheets("Sheet2").Range("J7").Value ?

 

[Noz2k]

just a location name ie "manchester"

 

[Noz2k]

Have got it working

Instead of using the column reference like ("A:A") I used the named range reference ("Location") which falls in that column.

Thanks for your help