Search code examples
excelvbaworksheet-function

How to find a character in a string using VBA

I have getting passed from another funtion, foldername like foldername = "a\\b\\c" or foldername = "a" and i'm trying to find the folder name contains "\\" and substitute "\\" with "__", split foldername based on "__" and pass into an array.

please have a look at what i was trying till now.

sample values: 

If WorksheetFunction.Find("\\", foldername) = 1 Then
       foldername  = WorksheetFunction.Substitute(foldername, "\\", "__")
       SheetNames() = Split(foldername, "__")
End If

i'm getting the below error.

enter image description here

Solution 
  • Dim a as Integer

'consider current value of is `foldername  = "a\\b\\c"`

a = InStr(foldername, "\\")

'if InStr is not able to find the value funtion returns 0

If a <> 0  Then
       foldername  = Replace(foldername, "\\", "__")
       SheetNames() = Split(foldername, "__")
End If

    Thanks for your suggestions guys, appreciate it.