Problem with Functions that use While but the Expression Returns #Value! RRS feed

  • Question

  • I am trying to write a basic VB subroutine that searches a string, looks for each instance of text, and removes it.  The text always starts with a specific word, and ends with a specific word, but can have variable data in between.  It can, and typically does, appear multiple times in the string being searched.

    Simplified example:


    So I'd want to search for '[' and ']', and remove them along with whatever's between them.

    I use this to find the characters in question:

    a = WorksheetFunction.Search("[", <string>)

    b = WorksheetFunction.Search("]", <string>)

    Then I remove the "[asdf]" with this:

    WorksheetFunction.Mid(<string>, 1, a) & WorksheetFunction.Mid(<string>, b, len(<string>)) 

    That works great, if the [text] only occurs once in in the <string>.

    But if I want to do it recursively, to get all of them, I do a While-Wend

    While WorksheetFunction.Search("[", <string>)

    'Do the stuff


    That works until the WorksheetFunction.Search returns #Value!.  Then the whole subroutine bombs!

    It doesn't help to check IsError(WorksheetFunction.Search), even though that should evaluate to True.

    Please Help!

    Thursday, July 13, 2017 8:35 PM

All replies

  • That is a one liner, for all cells on the sheet and all instances of text between [ and ]:

        Cells.Replace "[*]", "", xlPart

    If you are using VBA code to look for a string that may not be there, then use Instr

    If Instr(String,Lookfor)> 0 Then

    'LookFor was found

    End If

    Thursday, July 13, 2017 9:05 PM
  • Hi HDDabbs,

    is your issue is solved now?

    I find that after creating this thread , you did not done any follow-up.

    if your issue is solved now then I suggest you to update the status of this thread and mark it as an answer.

    if your issue is still exist then I suggest you to check the suggestion given by the community member may solve your issue.

    if you think that it can solve your issue then you can mark it as an answer.

    if then also you have any further questions regarding this issue then let me know about that.

    we will try to provide further suggestions to solve your issue.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Thursday, July 20, 2017 1:37 AM