Check (validate) repeating sequence

Hello,

Sorry for the lengthy post, just want to give as much detail as possible!

I need help writing a macro that will examine column A row by row to make sure a particular repeating sequence of 3 components is maintained (123-123-123 etc). I guess another term you techies would use is “validate”? If a component is missing, then I would like the “missing slot” to be filled in (inserted in proper position) with a predefined value. The sequence is as follows:

· *** First component: The first row (A1) must contain only a numeric value (example: 123456). If missing, replace missing value with: “000000”


· ***Second component: The second row (A2) must be alpha-numeric with multiple words (example: 14K 5-DIA S BAR BAND D.75TW) If missing, replace missing value with: “Product Detail”


· ***Third component: The third row (A3) contains “;QTY” (without the quotes) at end of a numeric/alpha-numeric string (3 examples: $15;QTY OR $1,000;QTY OR size: 16 - $30.35;size:20 - $36.60;size;24 - $43.74;size:30 - $90.10;QTY). NOTICE HOW ALL THE PRICING EXAMPLES END WITH “;QTY”. If missing, replace missing value with: “Pricing of product”


· The above sequence repeats itself until no more data exists in column A.

Here is an example of 5 cycles of the sequence:

(row1)350267
(row2)14K 8MM FACETED SMOKEY QUARTZ NECKLACE, 18
(row3)$136.00;QTY

(row4)350415
(row5)14K AMBER & FW PEARL FANCY ADJ. NECKLACE, 16"-18
(row6)$1,454.45;QTY

(row7)350539
(row8)14K BLACK PEARL NECKLACE 6- 6.5MM, 16" FRESHWATER
(row9)size:16 - $30.35;size:20 - $36.60;size:24 - $43.74;size:30 - $90.10;QTY

(row10)350629
(row11)14K 3-STRAND NECKLACE W/ PEARL & TURQUOIS (INSIDE 16"/O 18
(row12)size:16 - $238.00;size:7 - $102.00;QTY

(row13)351007
(row14)14KW BLACK TAHITIAN & DIAMOND 3 PEARL NECK 18", 9-12MM,.16CT
(row15)$754.00;QTY

(row 16 . . .)This sequence repeats itself until NO MORE data exists in column A.

So far I have tried this:

Please Login or Register  to view this content.

When I delete the forth line (the second item number) and use the given code, ALL the item numbers following the 4th line are changed to "0" AND replaces ALL descriptions below the 4th line with current pricing AND replaces ALL current pricing below the 4th line with the alternate text for pricing (Pricing for Product).

I am not very good at coding yet. Any help on this would be GREATLY appreciated !

Thank you!

Thank you Leith!

I get the same problem with this script as I was with the one I sent you?

When I delete the forth line (the second item number) and use the given code, ALL the item numbers following the 4th line are changed to "0" AND replaces ALL descriptions below the 4th line with current pricing AND replaces ALL current pricing below the 4th line with the alternate text for pricing (Pricing for Product).

Thank you for your time!

Originally Posted by Leith Ross

Hello imimin,

I am really not seeing where the problem is in your first post and the last one since the macro does not delete any lines when run. Are you manually deleting rows and then running the macro?

The whole purpose of the script is to check for lines in the sequence (column A) that are missing. Therefore, yes I am deleting rows to check the script for proper operation. Please re-read my initial post for details.

Originally Posted by Leith Ross

Hello imimin

You said in your post this sequence of three checks would be repeated until there was no more data. The implication was there would be no breaks or blanks between the rows of data and you were simply validating for content. Now you want to check for sequential integrity as well. Both can be done, but not by either method already posted. A new macro will need to written to validate the data and the sequencing.

I don't know a lot about this, just trying to find some help on doing it. Do you know how to do this?

Thank you!

Originally Posted by Leith Ross

Hello imimin,

Yes, I can write the macro for you but I will need some time to code and test it.

Thank you sir!

Originally Posted by Leith Ross

Hello imimin,

Okay, if needed then a whole row would be inserted and the default data added. Not a problem.

That is correct, thank you!