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

If you search for strings, it returns the position of the first occurrence
of the string. Numbers are individual charcters and are treated
differently. 1 is 1 even though it may be part of 15 or 120 or 351 or 218.
InStr would find the first occurrence of 1. Maybe you wnat to use InStrB
which searches by byte.


"CG Rosen" wrote in message
...
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

The InStr function cannot return the numbers you seem to be indicating it
does (InStr returns a position number, not a count), which probably means
you have code that you have not shown us. It is kind of hard to give you
advice if we don't know what you are attempting to do. Post the code you
have (working or not) so we can get an idea of what you are trying to do. If
you can supplement that with a description of what you want your code to be
doing, that would be good as well.

--
Rick (MVP - Excel)


"CG Rosen" wrote in message
...
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

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

Thanks for helpful input from you all. Dave's answer is the one that covers
my needs the best.

Thank you

cgr

"Dave Peterson" skrev i meddelandet
...
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

Here is a shorter, slightly different approach to do what you want...

Sub Test()
Dim myStr As String
Dim cVal As Long
Dim HowMany As Long
myStr = "1,21,31,1,42"
cVal = "1"
HowMany = UBound(Split(Replace("," & myStr & _
",", " ", ""), "," & cVal & ","))
MsgBox "There are " & HowMany & " occurrences of """ & _
cVal & """ in """ & myStr & """"
End Sub

You can test HowMany in your code if you need to differentiate between none
and some occurrences. Using Dave's If..Then approach, just do this after you
assign the value to the HowMany variable as in my above code...

If HowMany = 0 Then
MsgBox "Not found"
Else
MsgBox "Woohoo! Found it"
End If

--
Rick (MVP - Excel)


"CG Rosen" wrote in message
...
Thanks for helpful input from you all. Dave's answer is the one that
covers my needs the best.

Thank you

cgr

"Dave Peterson" skrev i meddelandet
...
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

It looks like you have CSV data. Use the spl;it function to put the various
numbers into an array

NumStr = "1,15,21"
MyArray = split(NumStr,",")

for each Num in MyArray
'put your code here
next Num

"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

Hi,

Yiu could do this. the message boxes are to illustrate it is working and
wouldn't be necessary in your final code

Sub SplitOut1()
searchstring = "1"
Dim numstring As Variant
numstring = "99,15,21,1"
b = Split(numstring, ",")
For x = 0 To UBound(b)
If b(x) = searchstring Then
found = True
Exit For
End If
Next
If Not found Then
MsgBox searchstring & " Not Found"
Exit Sub
End If
'do things
MsgBox searchstring & " Found"

End Sub

Mike

"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

Does this help?

Sub searchnumberinstring()
Dim mn As Integer
c = ActiveCell
mn = 5
mnl = Len(Application.Trim(mn))
'MsgBox mnl
For i = 1 To Len(c)
'MsgBox Mid(c, i, mnl)
If Mid(c, i, mnl) = mn Then
Exit For
End If
Next i
MsgBox mn & " found at position " & i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CG Rosen" wrote in message
...
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