VBA syntax question index match

[Arie Bos]

Hi everyone,

I have the following code creating an error in the syntax of the "TruckType" argument in the Match function. I am not sure if I should use Val(), Me.() or any argument, all gave me an error.

Dim TruckType As String
    Select Case True
      Case optTruckType11: TruckType = "Truck < 10 ton"
      Case optTruckType21: TruckType = "Truck 10-20 ton"
      Case optTruckType31: TruckType = "Truck > 20 ton"
    End Select

Dim CO2PerTruckload As Double
CO2PerTruckload = WorksheetFunction.Index(Range("CO2_TruckWeightWTW"), _
                  WorksheetFunction.Match(TruckType, Range("CO2_TruckWeight"), 0))

 

[Arie Bos]

May be I should add that

Range("CO2_TruckWeight")

contains the three items from the Select Case function.

 

[Arie Bos]

I solved my own question. The code was actually OK, but in a wrong place...