next empty cell in column, from cell that contains "xxx"

Hello.
I have this useful piece of code that enters data to the next empty cell in a specified column.

Please Login or Register  to view this content.

I wonder if there is a way to manipulate it so that it finds the cell in column "A" that contains the text "xxx", and then finds the next empty cell down from there...

Any ideas? Cheers.

Try (Change references where required.)

Please Login or Register  to view this content.

I've tested this along with adjustments and it doesn't seem to work. I believe there are 2 issues..

1) If there is text somewhere below "xxx", on the way up looking for the next empty cell it will stop there, as oppose to going past it and to the next empty cell below "xxx"

2) As it goes through each cell in MyRange, it enters them all in the same cell on the active worksheet. As oppose to listing them. So I end up with with last value in one cell and that's it.

Thank you for the suggestion though, I will keep investigating and looking for a way to make it work in the mean time.

Originally Posted by mruu

I've tested this along with adjustments and it doesn't seem to work. I believe there are 2 issues...

Did you actually try the code as it was posted? I ask because you are talking about "looking up" when jolivanes' code is "looking down".

I did... I say that because the data wasn't being entered in the next blank cell after "xxx". It was going into just the next blank cell from the bottom. So seemed like it was going UP.
I thought that was odd as well but that is what was happening.

I've started writing this code instead using .Find, I'm just trying to figure out how to replace "Range" with "value" or something..

Please Login or Register  to view this content.

Originally Posted by mruu

I say that because the data wasn't being entered in the next blank cell after "xxx". It was going into just the next blank cell from the bottom.

That means the "blank" cells you think you have are not actually blank... there is something in them even if it is just the empty text string ("").

Sorry, no, I just realised how it's working.
There is an empty cell immediately after "xxx" so (xlDown) takes it to the next occupied cell. Then offsets from there.
I need to get to the next empty cell after "xxx"

Ok this is my solution that seems to do the job:

Please Login or Register  to view this content.

No selecting/activating needed.

Please Login or Register  to view this content.

As long as the "xxx" is not in the first row.

Perhaps

Please Login or Register  to view this content.

Thanks everyone for your help. As mentioned, I came up with a solution that works. For what I'm doing I do actually need to activate a cell.

Jindon & Rick, your lines do work perfectly as well.

Jolivanes there's still an issue with yours where it keeps going to the same cell below "xxx".

Thanks again all, really appreciate it.

Originally Posted by mruu

For what I'm doing I do actually need to activate a cell.

My code and jindon's code do not need any cells to be activated to work, but you can always add a line of code to either of them in order to activate a cell for later use in other procedures. For my code...

Please Login or Register  to view this content.

Yes, but I mean further along I need a cell to be activated.. for following procedures.
And yes that is exactly what I did to your code to make it work for me, thanks Rick!

Re: where it keeps going to the same cell below "xxx".
I don't really know what you mean by that but it doesn't matter as your problem seems to be resolved.
It works for me with all different scenarios.

mruu,

The big difference between my code and the others is no error trapping is needed even if there is no "xxx" in col.A.

Originally Posted by jindon

The big difference between my code and the others is no error trapping is needed even if there is no "xxx" in col.A.

Given the purpose of the code is to find the blank space after the cell containing "xxx", it is hard to imagine the OP running the code when there was no "xxx" which is why I omitted error checking for that situation in my code. If, however, it was the case that there was no "xxx" in any of the cells in Column A, I would note that your code goes ahead and assigns the value to the next blank cell in the column anyway... I have to wonder if that is something the OP would want if such a circumstance were possible. Given that I don't think the OP would run the code without an "xxx" present, I think either of our codes would be fine for the OP to use.

I posted to the OP, not you.

If OP needs to check the existence of "xxx", I will add that line to my code, THAT'S ALL.

Originally Posted by jindon

I posted to the OP, not you.

If OP needs to check the existence of "xxx", I will add that line to my code, THAT'S ALL.

Whoa, obviously somewhere along the line I have done something to upset you... for whatever that was, I apologize. I have never meant any disrespect toward you (or anyone else for that matter) in any of the posting we shared. The only reason I posted the response to your message is you pointed out a difference to the OP between your code and the other posted code of which mine was one. Since you deliberately pointed out that your code did not need error checking, I thought it important to point out to the OP why mine did not include any error checking, in case my assumption for doing so was wrong, the OP would be alerted.