1
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

Pedro Lastra
  • 63
  • 1
  • 6
  • What is `C`? And what does `H + 2` mean - column J? – Alex P Feb 01 '16 at 10:56
  • you're using `"*"` as a wild card ? – Fnaxiom Feb 01 '16 at 11:02
  • What im trying to do there is that SM means anything that has inside the word SPOTMARKET. And yes as a wild card – Pedro Lastra Feb 01 '16 at 11:04
  • Yes, that's what a wild card means (the `*`stands for anything). You'll have to used `LIKE` operator for comparison. Check this thread. http://stackoverflow.com/questions/18409418/excel-vba-select-case-if-activecell-like-string – Fnaxiom Feb 01 '16 at 11:05

2 Answers2

1

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.

Alex P
  • 12,249
  • 5
  • 51
  • 70
  • This works perfect but how do i add another word ? im trying to add one more word like Spotmarket tothe InStr but it gives me error all the time. – Pedro Lastra Feb 01 '16 at 11:20
1

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

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • How come? In my console the following: ?"LALALALA SPOTMARKET LALALALA LALALA" like "*SPOTMARKET*" returns true, thus it should. Note - The stars before and after the second spotmarket are removed from stackoverflow. – Vityata Feb 01 '16 at 11:22
  • it asks for something liek = > > ...etc – Pedro Lastra Feb 01 '16 at 11:27