Find range of text, parse and copy (for..each?) rows until next range found

**purgamentum** · 05-06-2020, 10:06 PM

I've got an export from a program that will be pasted into an empty sheet. It'll look roughly like this:

Please Login or Register  to view this content.

Looking to have VBA run through that column; search for cells starting with L, parse the number range (ie 40-43), copying all to a new sheet.
The new sheet will end up starting with L1, next row (then bunch of rows of copied text) then on another row L2 (bunch of rows), L3 (rows), L4 (rows), etc. etc.

So the rows L40-43 above will end up looking as:

Please Login or Register  to view this content.

Added an example .xls; sheet 1 shows what is imported, sheet 2 results needed.

Sorry for the excessive rows in this post

to explain.

**jindon** · 05-06-2020, 11:37 PM

Here's how I read your problem and the result is slightly different from yours, so you need to verify.
Out put the result in Col.C

Please Login or Register  to view this content.

**purgamentum** · 05-07-2020, 12:20 AM

Originally Posted by jindon

Here's how I read your problem and the result is slightly different from yours, so you need to verify.

That's absolutely fantastic!

Exactly what I was hoping to achieve.
Thank you so very much jindon .. looking at that code, I'd be here 'till 2022!

**jindon** · 05-07-2020, 12:23 AM

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

**purgamentum** · 05-07-2020, 12:36 AM

Odd. I deleted the column and ran again, with different result - unless I wasn't seeing it the 1st time around. A1 starts with L17, the results now start with L0 and continue and L17 (I pasted to the right for a smaller /snip) are those of L18:
Attachment 676479
First run through I was sure it was exact.

edit: I can have the other program change the "L" (which I probably should); would this be easy to change to "Level" instead?

**jindon** · 05-07-2020, 12:38 AM

Your attachment is invalid, so can not open.

If you upload a workbook, it will help.

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**purgamentum** · 05-07-2020, 12:43 AM

Here's the .xlsm
sheet 1 has the results and I added a sheet 2 with Level instead.

**purgamentum** · 05-07-2020, 12:50 AM

Sorry. I must have messed something up. My old file was .xlsx, so I saved as .xlsm and it went all odd.
I reopened the .xlsx, added your code again, saved as a a different .xlsm and it works as it did the first time.

**jindon** · 05-07-2020, 12:50 AM

Is "Level" only the possible word begin with "L"?
Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**purgamentum** · 05-07-2020, 12:53 AM

odd results with the

Please Login or Register  to view this content.

replacement

But yes, "Level" would be the only L related word. I figure there's a change a code may come through one day with an L starting it, but never the word level.

**jindon** · 05-07-2020, 12:55 AM

Do you want to include any word begin with "L" as a header line?

**purgamentum** · 05-07-2020, 12:55 AM

Negative, just Level

**jindon** · 05-07-2020, 12:56 AM

So "L" and "Level"?

**purgamentum** · 05-07-2020, 12:59 AM

No, just Level .. I'll have the other program switch the "L" to "Level"

**jindon** · 05-07-2020, 01:01 AM

That's confusing, your workbook has "L" and "Level".

**purgamentum** · 05-07-2020, 01:08 AM

Originally Posted by jindon

That's confusing, your workbook has "L" and "Level".

Sorry! I was goofing around with the cells on other sheets I deleted. Trying to do what your code does.
my best effort was craziness like this:

Please Login or Register  to view this content.

**jindon** · 05-07-2020, 01:12 AM

I just need exact data and exact desired result in workbook showing the logic behind it.

**purgamentum** · 05-07-2020, 01:18 AM

ok, new workbook attached. sheet1 is what I have and sheet2 is the desired results.

**jindon** · 05-07-2020, 01:26 AM

OK, "Level" instead of just "L"...
You can now change such word to suite.

Please Login or Register  to view this content.

**purgamentum** · 05-07-2020, 01:34 AM

Can it skip levels? there's no 24 to 39, or 44 to 47; and 48 is missing.

**jindon** · 05-07-2020, 01:40 AM

OK, I see what you mean.. my assumption was wrong...

**jindon** · 05-07-2020, 01:44 AM

Try this one

Please Login or Register  to view this content.

**purgamentum** · 05-07-2020, 01:52 AM

Perfect!

Thank you jindon!

**jindon** · 05-07-2020, 02:00 AM

You are welcome.

**karmapala** · 05-07-2020, 02:29 AM

@jindon,

I want to learn your codes, will it be OK to ask them here ?
Or would it be better I open a new thread to ask about your codes ?

**jindon** · 05-07-2020, 02:34 AM

You can ask here, but I'm out at the moment.
So, my reply will be after 2,3 hours from now.

**karmapala** · 05-07-2020, 03:18 AM

Thank you, Jindon.
Take your time to reply, no need to hurry

.

I will ask your code on #2.

Please Login or Register  to view this content.

I see in the local windows that the last of a is a(39,1) with Empty value because cell A39 is blank.
If I change (2) to (1) (or when I remove the "(2)"), in the local windows the last one is a(38,1) with the value of cell A38.

1. I wonder why it needs (2) ?

Please Login or Register  to view this content.

2. would you please explain what the line above do ?
What I can guess is only the if(left(a1:a10000,1)="L" which is :
if the first character in any cell within cell A1 to A10000 is "L".....
and this line is to get in what row where the cell first character value is "L",
but how does the code work, I don't understand at all,
I even don't understand what does the "[ ... ]" sign means, have tried to google it but can't find the result

Please Login or Register  to view this content.

When I see the local window, my guess, this line is to get how many times of "L" will be written.
Assuming my guess is correct, I don't understand ...
3. why it needs "+ 1" ?

I want to understand those three lines first, the rest is later.

Thank you, Jindon.

**jindon** · 05-07-2020, 04:04 AM

1)

Please Login or Register  to view this content.

[] are the short cut notation of Evaluate method.
It calculates the formula in the brackets and return the value or an array as a result depends on how you write the formula.
So, x will be the array that holds the row references that the cell in col.A begin with an "L".
Since above row references are the row of "L", loop needs to set one row below "L" to one row above next "L" row.
Therefore we need to add one row below the last cell of the data set to get last "Next" "L", so that actual last row of "L" can be calculated.

2)
Under above reason, need to expand the data range up to one row below the actual data range, otherwise it errors "Out of Range".

3)
That was how I first assumed the logic, so the latest code doesn't have that line.

**karmapala** · 05-07-2020, 05:20 AM

@Jindon,

Thank you very much for the explanation.
Now I'm starting to play around with your code by trying to separate it little by little to see what is the result.

For example, when I do this :

Please Login or Register  to view this content.

I see that the result of x is the row number value if the first char is "L" and FALSE value if the first char is not "L"

Now with your complete code :

Please Login or Register  to view this content.

I think it filter out the FALSE value, and now I'm a bit clear on how the code works
and now I understand what you mean in your sentence :

x will be the array that holds the row references that the cell in col.A begin with an "L".

.

Since above row references are the row of "L", loop needs to set one row below "L" to one row above next "L" row.
Therefore we need to add one row below the last cell of the data set to get last "Next" "L", so that actual last row of "L" can be calculated.

2)
Under above reason, need to expand the data range up to one row below the actual data range, otherwise it errors "Out of Range".

Thank you very much for the explanation about this.

That's all for the time being.
I hope you don't mind if later I ask another question after I continue to learn your next lines but don't understand it.

regards.

**jindon** · 05-07-2020, 05:27 AM

It is very important to understand the code given.
Most of the cases, no one ask the details, but just ask to add the explanation for each line.
That's useless and I believe most of the people don't mind reading the comments anyway.

If you ask how you are asking, debugging for yourself with Local Window open, it will so helpful and I can feel that you are getting better understanding.
You can ask anytime and I will respond as long as I can.

**karmapala** · 05-07-2020, 05:52 AM

Yes, I learn so much from you specifically and from this forum generally.
Sometimes when I see your code here and there, it made my headache, but it's so interesting for me to learn

.

And yes, I get better understanding than before.

Thank you once again, Jindon.
I repeat a comment from one of the member I read about you : "you are rock"

.

**purgamentum** · 05-07-2020, 11:22 AM

hi Jindon .. I looked over a ridiculous goof-up on my request. As Karmapala said above, your code is astounding to see and when I saw and tested your first post I totally forgot a major point.

You'd placed the results in column C; whereas I was hoping for a new sheet. Reason being, in reducing my excel sheet to a sample sheet I took everything out of all the other columns since they were irrelevant (almost ?! ) to the code I was hoping for.
Except that I was needing the row copied, not just the cell! I feel like a fool

... could you please adjust so that it'll copy the rows with all their content to a new sheet?

Attached a final sheet.

Please please

**jindon** · 05-07-2020, 09:30 PM

Originally Posted by purgamentum

As Karmapala said above

OOps now I realise that it was not you asking about the details of the codes.
Sorry for interrupting your thread, but I hope it also helped you to understand better...

Try this.

Please Login or Register  to view this content.

**purgamentum** · 05-07-2020, 09:43 PM

Originally Posted by jindon

OOps now I realise that it was not you asking about the details of the codes.
Sorry for interrupting your thread, but I hope it also helped you to understand better...

Try this.

Works great, thank you again!
Also, don't mind at all about the coding questions. Yes, I did read trying to follow along!

Find range of text, parse and copy (for..each?) rows until next range found

LinkBack

Thread Tools

Rate This Thread

Display

Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Re: Find range of text, parse and copy (for..each?) rows until next range found

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find substring in range and categorise cells in range based on substring found

[SOLVED] find a range to insert a formula and copy it across for new rows

Find a string in specified cell range if found copy the cell

[SOLVED] How to find text then select RANGE form the text found to lastrow of Column J

[SOLVED] To Find every instance of TEXT copy, repeat until no Find found

[SOLVED] Find cell in Range and If found then copy to next sheet

.Find problem when the found range's rows go from single to double digits

Bookmarks

Bookmarks

Posting Permissions