"Type Mismatch" picking Select Case from ComboBox

[matonanjin]

I hope my post title was descriptive, prolly not.

I am picking from a combobox which takes the list index to use in a Selct Case. That selection determines what is stuck in a cell. There are 11 choices in the combobox. That choice goes to this Select Case:

Code: Select all

  Select Case ComboBox2.ListIndex         'condition of vinyl   DOWN THE ROAD IF FORMAT IS NOT VINYL I SHOULD NOT HAVE THESE CHOICES???
        Case 0
            Range("f" & intRowNo) = "M"
         Case 1
            Range("f" & intRowNo) = "NM"
        Case 2
            Range("f" & intRowNo) = "VG+"
        Case 3
            Range("f" & intRowNo) = "E"
        Case 4
            Range("f" & intRowNo) = "VG"
        Case 5
            Range("f" & intRowNo) = "G"
        Case 6
            Range("f" & intRowNo) = "G+"
        Case 7
            Range("f" & intRowNo) = "G-"
        Case 8
            Range("f" & intRowNo) = "P"
        Case 9
            Range("f" + intRowNo) = "F"
        Case 10
            Range("f" + intRowNo) = "S"
  End Select

As you can see, the Case determines what is stuck in the Row intRowNo Column F.

Everything works just spiffy if I choose Case 0 through 8. If I choose 9 or 10 I get the "Type Mismatch". Any idea why the last two choices are disliked by VBA?

[HansV]

Why did you use + instead of & for those two? I suspect it will work if you change

Code: Select all

        Case 9
            Range("f" + intRowNo) = "F"
        Case 10
            Range("f" + intRowNo) = "S"

to

Code: Select all

        Case 9
            Range("f" & intRowNo) = "F"
        Case 10
            Range("f" & intRowNo) = "S"

By the way, I'd store the values in a (hidden) column of the list box, for example in the second column. You can then use

Code: Select all

    If ComboBox2.ListIndex <> -1 Then
        Range("F" & intRowNo).Value = ComboBox2.Column(1)

(Columns start counting at 0, so Column(1) is the 2nd column)

[matonanjin]

Why did I use + instead of &?!? Isn't it obvious? I don't have an effing clue what I am doing. That is why.

Store the value in a hidden column of the list box? To make sure I understand. So if I click on the choice of the combobox that will pull the (hidden) choice and actually eliminate the need for the entire Select Case, wouldn't it? Sheez. Embarrassingly obvious.

Thank you for the help and suggestion.

[HansV]

Store the value in a hidden column of the list box? To make sure I understand. So if I click on the choice of the combobox that will pull the (hidden) choice and actually eliminate the need for the entire Select Case, wouldn't it?

Yep!

[matonanjin]

Yep!

Hans,
Thank you again for taking the time to look at my code and respond with your helpful suggestion/correction. Know that it is appreciated. As you suspected, making the change did immediately resolve my problem. And I am going to go back and change the combobox to hold the hidden column which will be much cleaner.

[snb]

This is all you need:

Code: Select all

cells(intRowNo,6) =choose(ComboBox2.ListIndex+1,"M","NM","VG+","E","VG","G","G+","G-","P","F","S")

or

Code: Select all

cells(intRowNo,6) =Arraty("M","NM","VG+","E","VG","G","G+","G-","P","F","S")(ComboBox2.ListIndex)

or

Code: Select all

cells(intRowNo,6) =Split(,"M NM VG+ E VG G G+ G- P F S")(ComboBox2.ListIndex)