Using the InStr Function in the Selection Window

Question

This does not work. Is there a way to do what I'm trying to do here? I cannot select the case if the value is in the given bite:

Select Case gTTD.Cells(r, 4)

     Case InStr(gTTD.Cells(r, 4), "MASTER LOG")
         resp = "MM LOG"
      Case InStr(gTTD.Cells(r, 4), "MASTER MET")
         resp = "MM MET"
     Case "PIR"
         gTTD.Cells(r, 7) = "Martin Trépanier"
         resp = "Martin Trépanier"

 End Select

I understand why this work does not work, but is there a way to make it work? thanks

thanks

Answer 1

Here's a little trick I'm using, the select statement just wants to find the same results. Here is a simple example:

    Select Case True
        Case (1 = 2)
            Stop
        Case (2 = 3)
            Stop
        Case (4 = 4)
            Stop
        Case Else
            Stop
    End Select

This will fall into case 4 = 4. In your example, this might be a simple answer:

Select Case True

     Case (InStr(gTTD.Cells(r, 4), "MASTER LOG") > 0)
         resp = "MM LOG"
      Case (InStr(gTTD.Cells(r, 4), "MASTER MET") > 0)
         resp = "MM MET"
     Case else
         gTTD.Cells(r, 7) = "Martin Trépanier"
         resp = "Martin Trépanier"
 End Select

Answer 2

try it

dim r as integer
for r = 1 to 'number of rows
    if Instr(gTTD.Cells(r, 4), "MASTER LOG") <> 0 then
         resp = "MM LOG"
    elseif Instr(gTTD.Cells(r, 4), "MASTER MET") <> 0 then
        resp = "MM MET"
    else
        gTTD.Cells(r, 7) = "Martin Trépanier"
        resp = "Martin Trépanier"
    end if
next r