Loop problem

Hello Jadee77,

You need to change First to Dl on your Instr statement...

Please Login or Register  to view this content.

This question really should be a FAQ. I see it time and time again and looping just isn't the best way of doing this sort of thing.

Whenever you find yourself creating looping code stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop.

In this case use something like

Please Login or Register  to view this content.

This uses just one Copy / Paste instruction rather than the many you would if you're looping through many cells.
In your original your variable 'first' was not being changed anyway. It retained its C2 value all through

@ Richard Buttrey,

Why do you always through a spanner in the works, just to proof that you are correct?

I agree with you my friend!

Keep on going strong.

Kind Regards.

I think it is in the best interest of the student if the teacher can connect on the student's level of understanding. There will be plenty of time to revisit and improve as does the student's knowledge.

Originally Posted by Richard Buttrey

Please Login or Register  to view this content.

You are trying to grab everything after the comma, correct? To do that using the RIGHT function would require you to subtract the result of the FIND function from the length of the entire text string minus an additional 1 to adjust for the subtraction... or, alternately, you could use the MID function and specify a large enough number for the third argument. So either this...

"=IFERROR(RIGHT(C2,LEN(C2)-FIND("","",C2)-1),"""")"

or this...

"=IFERROR(MID(C2,FIND("","",C2)+2,99),"""")"

Note: The subtraction of 1 in the first formula and addition of 2 in the second formula assumes there is a space after the comma. If this is not the case, then remove the -1 from the first formula and change the +2 to +1 in the second formula.

Now, with that said, we can remove the IFERROR function call entirely from the second formula by simply making sure the FIND function can always find a comma...

"=MID(C2,FIND("","",C2&"","")+2,99)"

And again, the +2 assumes there are spaces after the commas... change it to +1 if there will not be spaces after the commas.


EDIT NOTE: Here is another way to write a non-looping macro to do what the OP asked for. This macro could possibly be faster as it eliminates placing formulas in Column D only to Copy/Paste the calculated values over top of those formulas...

Please Login or Register  to view this content.

To summarize.

Please Login or Register  to view this content.

The last one is an array method which in speed comes the closest to the formula ways.

@ Rick

Tested on 10K rows

getfirst = 0.14 sec

getfirst2 = 0.15 sec

Grabfirst = 0.18 sec

I was under the impression that working in memory would always be the fastest but from previous tests I noticed that putting in formulas and then converting to values

was always faster, no matter how large the testrange. That's why I wrote 'comes closest to' knowing that the formula way would always be slightly faster.

@ Leith Ross,

Hi Leith, a very valid point, and I would even add to it as follow;

I think it is in the best interest of the OP's level of understanding. There will be plenty of time to revisit and improve as does the OP's knowledge.

Kind Regards.