Vlookup in VBA generating error

[JackDanIce]

Hi,

I'm trying to condense following:

    Select Case i
        Case 1 To 3:    ForeCastCol = "A"
        Case 4 To 10:   ForeCastCol = "B"
        Case 11 To 15:  ForeCastCol = "C"
        Case Else:      ForeCastCol = "D" 'Limit is 18 regardless
    End Select

To:

Application.Vlookup(i, [3, 10, 15, 18], ["A", "B", "C", "D"], True)

Where "A", "B" etc are irrelevant or an equivalent function

Limits of 3, 10, 15 and 18 will never change (and always ascending order) can someone suggest?

TIA,
Jack

 

[Fluff]

How about

Dim x As Variant, i As Long
i = 5
x = Evaluate("Lookup(" & i & ",{0,4,11,16},{""A"",""B"",""C"",""D""})")

 

[JackDanIce]

Much appreciated!

 

[JoeMo]

Or:

i = 5
x=Application.Lookup(i, Array(3, 10, 15, 18), Array("A", "B", "C", "D"))

 

[JackDanIce]

Array(), that's what I wasn't using for 2nd and 3rd arguments, thanks too @JoeMo!

 

[Fluff]

Much appreciated!

You're welcome & thanks for the feedback

 

[JoeMo]

Array(), that's what I wasn't using for 2nd and 3rd arguments, thanks too @JoeMo!

You are welcome - thanks for the reply.

 

[shg]

Your original code is faster, and superior, IMO.

 

[JackDanIce]

Hi @Fluff and @JoeMo, may have spoken too soon. This is returning Error 2042, I replaced "5" with i (after realising I was getting the same results for each loop!)

Private Function ForeCastCol(ByRef i As Long) As Variant
        
    ForeCastCol = Application.Lookup(i, Array(3, 10, 15, 18), Array("Total_", "Practices_", "Central_", "AffL_"))
    
    '& wMain.Cells(wMain.Range("Head_Job").Row, wMain.Range("Head_Cal").Column).Resize(, 18).Cells(1, i).Value
    
End Function

Can you spot my error please? I've removed the concatenate of a specific header value (based on i from a fixed header area) to try to isolate the cause but can't work it out.

 

[JackDanIce]

It is @shg? If I revert to that, then won't have these errors...

Thanks for the comment @shg, curious to know why this doesn't work now, but if Select Case is faster (and working) then, will revert.