Check tag presence every N lines of text

Test file.xlsx

Originally Posted by patel45

need a sample txt file (30 rows ) and desired result

Dear Patel,
Please, check the XLSX file attached.

Text in A2 is correct. Text in A3 is NOT correct.
I'd really need a macro that could tell me that there is an error in A3.
Any kind of message/highlight/morsecode would be OK!

Originally Posted by cytop

or try something like...[code]
[cut]

Dear cytop.
Alas, it seems that it doesn't work.
I've copied the code in the module and saved the file as "xlsm".
Then, I've selected three cells with text (two with incorrect text) and run the macro.
An "?" appeared at the end of each line and nothing else.

Did I do anything wrong?

Are you pasting text from a web page? Have you tried, Copying the text then use paste special, select html and it will put the text into separate rows. In this case an easy solution is possible.

Originally Posted by JJGF

Are you pasting text from a web page? Have you tried, Copying the text then use paste special, select html and it will put the text into separate rows. In this case an easy solution is possible.

Dear JJGF.
No, I'm not pasting from a web page.
Still, I can paste the text into Word, then paste back in Excel.
It will put lines into separate rows.

A few passages, but not a problem for me.
But then?

I'll try to the best of my knowledge,
Can you try first copy the text, then on the home tab (Excel 2010) click on paste with arrow down then select text?

Originally Posted by JJGF

I'll try to the best of my knowledge,
Can you try first copy the text, then on the home tab (Excel 2010) click on paste with arrow down then select text?

Thanks a lot, JJGF.
Yes, I can do what you ask, but I'm not sure of the expected result.

Note that I can copy/paste/manipulate the cells in any way needed.
I don't mind if the solution needs three steps and a few minutes of work.
If it works, I could spare literally hours

Originally Posted by Ivan F.

Dear JJGF.
No, I'm not pasting from a web page.
Still, I can paste the text into Word, then paste back in Excel.
It will put lines into separate rows.

A few passages, but not a problem for me.
But then?

If you are able to get the text into rows, try the attached file, just run the formulas down (in Col E and F) to cover all the text in the A column.

The formula will check every line with a <pg> and if there is not another <pg> within 4 lines down, it will say "check line"

Misunderstood your original requirement... it checked individual rows on the worksheet, not lines in a cell. I also can't understand where the '?' you mentioned comes from. To avoid any issues, copy of your workbook attached. This works with your original layout where the text is in cells rather than rows.

Run the code in the worksheet class module for sheet1 manually...

Test file.xlsm

Originally Posted by JJGF

The formula will check every line with a <pg> and if there is not another <pg> within 4 lines down, it will say "check line"

Ingenious indeed
It needs a tweak or two (such as adding a <PG> tag at the beginning and at the end of each original string: 10 seconds of work with a formula), but I'm pretty sure it'll work.

Thank you, JJGF!

Originally Posted by cytop

Run the code in the worksheet class module for sheet1 manually...

A few "run the code" later…
It works like a charm!

Using Excel for Mac, I changed the delimiter (from VBLF to VBCR), and now it works on my Mac as well.

Marked the thread as SOLVED.
Added reputation to your last post.

Please, Cytop, let me know via PM if I can thank you with a little gift
(Amazon/Spotify/PSN card or something similar)

Originally Posted by Ivan F.

Ingenious indeed
It needs a tweak or two (such as adding a <PG> tag at the beginning and at the end of each original string: 10 seconds of work with a formula), but I'm pretty sure it'll work.

Thank you, JJGF!

Glad to help, I only wish I could provide something better with VB

Originally Posted by cytop

A very kind offer, but I'll keep the reply public...

THe Rep is payment enough, thank you. However, if you feel the need to dispose of some of your hard earned cash then please consider a small donation to a charity - my choice would be dogs or kids (after rearing both I know which I prefer!) - but the choice is yours.

Dear Cytop,
I'll do this, thanks again.

A little note for other users who wants to try the code…
It seems it's still necessary to add a <PAGE> tag at the end of each cell, in order for the macro to run properly.
You can do this using a simple formula.
If your text is in column A, insert this in column B:

Please Login or Register  to view this content.

Then, copy back in column A the text as value.
A few seconds of extra work, nohing else.