Select Case/Lookup in VBA

[ccordner]

Hi

I'm trying to use VBA to work out what type of vehicle is supposed to be in a diagram.

So, for example:

AB1XX = Class 1
AB5XX, 6XX, 7XX = Class 2
BC 11X = Class 3
BC 12X = Class 4
CD 23X, 24X = Class 5.

I'm currently trying to use a Select Case statement, but the following doesn't work:

Function getClass(strDiagram As String)

Select Case strDiagram


    Case Left(strDiagram, 3) = "NH4"
    
        getClass = "150"
        
    Case Left(strDiagram, 3) = "NH3"
    
        getClass = "156"
        
    Case Else
    
        getClass = Left(strDiagram, 3)
         
End Select


End Function

Is there a neater way of doing this? There are many diagrams, so I don't really want to have to do too many select/case statements. What I really want to do is check within a range if possible.

Thanks
Chris

 

[offthelip]

I would do this by putting all the vehicles and diagrams in table on another sheet (which I have called "Data") this sheet can be hidden, then you can find the right diagram using this code:

Function getclass(strdiagram As String)

getclass = ""
With Worksheets("Data")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
End With
For i = 1 To lastrow
 If strdiagram = Trim(inarr(i, 1)) Then
  getclass = inarr(i, 2)
 End If
Next i
End Function

If you don't want to have a hidden sheet, then what I do is using the same list of vehicles and diagrams on excel sheet, I use the list to write the vba code to define an internal array by appending the right bits of VBA to the list
using equations such as this:
="inarr("&C1&","&D1&")="""&A1&""""
where column C has the row number and column D the column number

 

[dmt32]

Hi,
checking within a range would be a more manageable solution

Add a sheet

Column A Enter All Vehicle Types

Column B Enter All Classes


The try update to your function

Function GetClass(ByVal strDiagram As String, ByVal Target As Range) As Variant
    Dim VehicleClass As Variant, m As Variant
    VehicleClass = Target.CurrentRegion.Value
    With Application
        m = .Match(strDiagram, .Transpose(.Index(VehicleClass, 0, 1)), 0)
    End With
    If Not IsError(m) Then GetClass = VehicleClass(m, 2)
End Function

call as follows

MsgBox GetClass(CarType, Sheets("Sheet1").Range("A1"))

Where CarType would be your variable to pass to function.

Change the variable name and sheet name shown in RED as required

Dave



Dave