Proper syntax to exit a loop if IsError is true

[gvaltat]

Hi,

As the title says it, I am currently stuck with a specific part of a project ( I am still new at programming and VBA).

My final goal is to count how many words are contained in each cell of a column.
To do that, and for 1 cell, my method is to count how many spaces there are, and to say that there is 1 more word.
To count these spaces, I want to use a loop which finds the first space, then the next one by starting at the location of the previous one +1

The procedure attached woks until there is no space left to find. Then I get an error message.
Any help to straighten up this coding would be very much appreciated.

Thank you for reading.

The attached file contains a procedure which purpose is to count the words in C4
IsError_WordsPerCell-2ndMethod.xlsm

[judgeh59]

take a look at the SPLIT functon

Please Login or Register  to view this content.

untested but I believe this should work....you don't have to check through the cell for spaces the Split command "splits" the Value by whatever delimiter you use and puts it in an array and then you can use Ubound (Upper Bound) + 1 to "count" the spaces....HTH

[judgeh59]

here is your code with my changes....

Please Login or Register  to view this content.

[stnkynts]

Formula:

Please Login or Register  to view this content.

VBA

Please Login or Register  to view this content.

[gvaltat]

Sorry for not answering earlier.
Thank you to both for your answers; they have been very valuable and instructive. I am always glad when I learn something else that what I was requesting
You have provided 2 different solutions to my problem, but they don't answer my question, which was about the proper syntax of IsError to have it working as intended within the loop. I can't mark the thread as Solved for this reason, but I am going to add to your reputation for the value of your help.
Thank you again!

[judgeh59]

I don't know why your IsError system isn't working...everything I look up shows that it uses a IsError(RtnValue) and the RtnValue is set from an expression (IE: a valid error number), so in theory it should work...but I wouldn't use this looping technique for this application....I believe both stnkynts and I have shown a different and possibly better way...I totally understand your wanting to understand why this doesn't work and I can only give a weak answer....sorry....HTH

Edit: the IsError didn't show in the actually looping construct....not that it can't, I just haven't seen it used that way....

[gvaltat]

Thank you for answering back. I am too new to VBA/programming to realize if what I am doing should be done one way or another one.

I have used your code for my project; I found it very clean and it works perfectly. Even after that, I have tried to understand why my loop doesn't work, trying several variations, but with no effect. I am just giving up

PS: I am not sure that I understand the first part of your Edit.

[judgeh59]

no problem on the "not understanding"...sometimes it works that way....in doing a little research I didn't see any examples that used the IsError like you were using it. That's all I was trying to say.

Excellent realization that there are usually at least 2 ways of doing something in XL and keeping an open mind really helps....kudo's to you....

I'm not sure why your code didn't work either, except, again, I haven't seen IsError used in that way...hope this helps....