Parse a String with commas in VBA 2

[logius]

I know it's possible to parse a string in VBA using some built-in function using a specified delimiter (in this case a ","). Could anyone help with the code to do this? The goal is to take the string stored in a cell on an Excel spreadsheet and break it up for comparison against a user-input value.

User Input : 23
Data in string : 43, 134, 23, 18, 246

If the data isn't in the string, then it should loop into the next row and begin the search again.

Now the loop isn't a problem, it's just the parsing of the of the string I need help with.

----------------------------------------
If you are reading this, then you have read too far...

 

[logius]

Nevermind, just found the InStr function. It does the job nicely.

----------------------------------------
If you are reading this, then you have read too far...

 

[SkipVought]

Hi,

Take a look at the Split Function.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[PHV]

Another way avoiding the slow Split process:
DataInString = "43, 134, 23, 18, 246"
UserInput = "23"
If InStr("," & Replace(DataInString, " ", "") & ",", "," & UserInput & ",") > 0 Then
MsgBox UserInput & " found in " & DataInString
End If


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[logius]

The search would only run through about 80 or so cells, so I'm not too worried about speed here. That's an interesting alternative, however that may be useful in another piece of code. Thanks, Skip and PHV!

----------------------------------------
If you are reading this, then you have read too far...