Grabbing string values from the end of a string of an unknown length

[Don Guillett]

I guess I'm just of the old KISS school. I tend to try to listen to what the
ORIGINAL poster said "STRING VALUE" and solve THAT problem. Sometimes, OPs
can become confused if it gets too fancy. However, you are correct.. I'm
surprised you didn't also berate me for not using dim as I should.

 

[Mike H]

Hmmm,

Having missed the development of this thread while travelling home I'm
simply amazed at the size and diversity of it. On the theme of KISS and
returning to the original request made by the OP what is wrong with the
original suggestion posted by myself?

I'm sure this now invites me to be shot down in flames but in defence of
that solution it variously:-

Provides an answer the OP recognised as solving the problem.

Was intuative in what it was doing so probably helped in his understanding.

Clearly wasn't elegant but I don't understand what the OP meant by this
except the lack of elegance was made in a comparison to a solution that
didn't work and was error prone.

Will not produce errors in the event of empty or cells not containing the
search string.

Doesn't give rise to the seemingly complicated issues of using FIND/FINDNEXT
which even MVP's seem to find difficult to agree on.

Is only 7 lines long (I agree it should be a couple more if variables had
been correctly dimensioned)

Executes as fast as any other offered solution (This test was limited to
searching ~ 5000 cells and sending the results to the immediate window.

Hasn't sent the OP to bed with a headache


So for the education of myself why does KISS not apply?

Regards,

Mike

 

[Mark Stephens]

Hi Mike,

Yes I tend to agree with both actually, your code works and I can understand
it so it serves its purpose well for me. On the other side of the coin, it
means I have to specify the size of my search area (I tried substituting the
reference to the range:

Set rTopRow = Sheets("FundList").Range("C1:AA1")

with

Set rTopRow = Sheets("FundList").Row(1)

but it doesn't work.

If I could do this then it would save me having to define the no of columns
in the top row to search but then even if i set it to the maximum in future
(Range("C1:XFD1") it has the same effect so no worries.


FindNext would be more efficient I guess in that it will just search until
the last one if found but I guess it depends what is your purpose for doing
it.... to get it working in a reasonable manner without being too slow or to
write the most elegant code possible!

I will save the latter for my retirement, at the moment I am grateful for
your assistance with the former.

Thanks again everyone, regards, Mark

 

[Rick Rothstein \(MVP - VB\)]

Try it this way...

Set rTopRow = Sheets("FundList").Range("1:1")

Rick

 

[Mark Stephens]

Thanks a lot Rik, Ican have my cake and eating it now