How about:

Option Explicit
Sub testme()

Dim myStr As String
Dim myStrToTest As String
Dim cVal As String 'just to show how
Dim cValToUse As String
Dim myPos As Long
Dim HowMany As Long

myStr = "1,21,31"
cVal = "21"

'remove any possible spaces
myStrToTest = Replace(myStr, " ", "")
'make sure each element has a leading and trailing comma
myStrToTest = "," & myStr & ","

'same thing with cVal
cValToUse = "," & cVal & ","

'now look for that modified value in the modified string
myPos = InStr(1, myStrToTest, cValToUse, vbTextCompare)

If myPos = 0 Then
MsgBox "not found"
Else
MsgBox "woohoo! Found it"
End If

'if the strings we
mystrtotest = ",1,21,31,21,"
cValToUse = ",21,"
'(after modifying them)
'and you wanted to count how many times 21 appeared:

HowMany = (Len(myStrToTest) - Len(Replace(myStrToTest, cValToUse, ""))) _
/ Len(cValToUse)
MsgBox HowMany

End Sub

CG Rosen wrote:

Good day group,

Trying to use the InStr function in VBA to find if various numbers is
contained in a string.

If InStr(c.value, "String") 0
Then..........................................etc

The c.value can be from 1 to 50. The string can look like "1,15,21" . If the
c.value =1 that number
is then found 3 times. How is it possible to do a distinction that 1 is only
found one time and 2 one time and say 21 one time and so on? Do I need some
other kind of separator in the string?

Hope my question is understandable.

Brgds

cgr

--

Dave Peterson