VBA checking cell against multiple ranges and returning a value

[R0chelle]

Hi everyone,
I'm not a master at excel but find myself somewhat capable of adapting what I find to my needs but I've been searching for hours unsuccessfully how to do this.
I am trying to match ratecodes to a list of breakfast prices per ratecode and inputing them in a column.
I have managed to do it with some ratecodes using case and it works fine but the list of ratecodes is so long I wanted to do it through range.

I have on my sheet "Tri" a list of ratecodes in column F. The length of the list changes daily.
On my sheet "data" I have named 4 different ranges of ratecodes : PDJDOUZE, PDJTREIZE, PDJQUATORZE AND PDJSEIZE
On my sheet "Tri" I want for each line, column G to show 12 if column F is part of PDJDOUZE, 13 if part of PDJTREIZE 14 if part of PDJQUATORZE and 16 if part of PDJSEIZE.
You are my only hope...

 

[Trebor76]

Hi R0chelle,

Welcome to MrExcel!!

If I've understood correctly this should be what you're after:

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    Dim nmeMyNamedRange As Name
    Dim dblMyVal As Double
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook.Sheets("Tri")
        For Each rngMyCell In .Range("F2:F" & .Cells(Rows.Count, "F").End(xlUp).Row)
            For Each nmeMyNamedRange In ThisWorkbook.Names
                'If the cell address of 'rngMyCell' is in any of the four named ranges then...
                If Not Intersect(Range(rngMyCell.Address), Range(ThisWorkbook.Names(nmeMyNamedRange.Name).RefersToRange.Address)) Is Nothing Then
                    '...set its value the 'dblMyVal' variable and output it to the column on the immediate right of 'rngMyCell'
                    dblMyVal = Evaluate("VLOOKUP(""" & nmeMyNamedRange.Name & """,{""PDJDOUZE"",12;""PDJTREIZE"",13;""PDJQUATORZE"",14;""PDJSEIZE"",16},2,0)")
                    rngMyCell.Offset(0, 1).Value = dblMyVal
                    Exit For
                End If
            Next nmeMyNamedRange
        Next rngMyCell
    End With
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[R0chelle]

Thank you for your quick response !
I've tried what you sent and nothing happens...
Not even an error...

 

[Trebor76]

That's odd as it worked for me.

If the cell address in Col. F of the "Tri" tab is not in any of the cell addresses of any of the four ranges nothing will happen.

 

[R0chelle]

Hi,
I'm back for some feedback.
I tried for a while to find why your coding didn't work for me then I went old school with a simple vlookup :

Sub Rate()
Sheets("Tri").Select
Range("G3").Select
ActiveCell.Formula = "=IF(ISNA(VLOOKUP(F3,Data!$A$6:$B$500,2,0)),""16.95€"",(VLOOKUP(F3,Data!$A$6:$B$500,2,0)))"
Range("G3").AutoFill Range("G3:G" & Range("A65536").End(xlUp).Row)
End Sub

Where I kept the lists and just put the prices next to the rates...
Not so elegant a solution, I will concede, but works fine now
Thanks for your help though

 

[Trebor76]

If I've understood correctly

I obviously didn't

Here's a more succinct macro to get the result (assuming you're using Excel 2007 or higher):

Option Explicit
Sub Rate()

    ThisWorkbook.Sheets("Tri").Range("G3:G" & ThisWorkbook.Sheets("Tri").Cells(Rows.Count, "F").End(xlUp).Row).Formula = "=IFERROR(VLOOKUP(F3,Data!B:C,2,FALSE),16.95)"
    
End Sub