CODEWITHSUNDEEP
vbaexcel
Pedro Lastra
Pedro Lastra

Reputation: 63

Excel macro Select Case

SM1 = "5000"
SM = "SPOTMARKET"
SM = "*" & SM & "*"

Select Case Cells(H + 2, 24).Value
            Case SM,SM1
             C(j) = "SPOTMARKET"

        End Select

I'm trying to make something like that but the "SM" doesn't work as it suppose to. I want that C(J) = "SPOTMARKET" when Cells(H + 2, 24) has the word spotmarket in it. it could be WRER-SPOTMARKET or the opposite SPOTMARKET-WRER.

This lines are inside For j=0 to XXX and For H=0 to XXX. C is an array.

THANK YOU

Upvotes: 1

Views: 111

Answers (2)

Alex P
Alex P

Reputation: 12489

You should use InStr to test for whether "SPOTMARKET" is found. 

If InStr(Cells(H + 2, 24), "SPOTMARKET") > 0 Then
    C(j) = "SPOTMARKET"
ElseIf InStr(Cells(H + 2, 24), "FORWARDMARKET") > 0 Then
    C(j) = "FORWARDMARKET" 
End If

InStr returns 0 if no match and an integer > 0 if found.

Upvotes: 1

Vityata
Vityata

Reputation: 43575

Edit: Now it should work:

Public Sub Testing()
    Dim sm1
    Dim k

    k = "lalal SPOTMARKET ale"
    sm1 = "5000"

    Select Case True
        Case k Like "*SPOTMARKET*", sm1
            Debug.Print "Found"
    End Select

End Sub

Here is an article with good examples for like:

https://msdn.microsoft.com/en-us/library/swf8kaxw.aspx

Upvotes: 1

Related Questions