{"id":11934,"date":"2022-09-29T01:38:24","date_gmt":"2022-09-28T20:08:24","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/"},"modified":"2022-09-29T01:38:24","modified_gmt":"2022-09-28T20:08:24","slug":"solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/","title":{"rendered":"[Solved] How to stop second run of the code to prevent override data regex vba?"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-43561271\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"43561271\" data-parentid=\"43560650\" data-score=\"1\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>One way to tell if the entry has been previously split is as follows<\/p>\n<ul>\n<li>If the regex.test fails, then\n<ul>\n<li>If the results line passes, then the item has been previously split<\/li>\n<li>if not, then it is a blank, or a malformed entry<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Note that a lot of this could be avoided if you were not overwriting your original data.  I would recommend against overwriting your data both for audit and debugging purposes, but the below should help in case you cannot change that.<\/p>\n<p>You just need to make some small changes in the logic where we checked for the malformed entry originally.  As well as reading in the &#8220;possible&#8221; results array into vSrc so that we have the potentially split data to compare:<\/p>\n<pre><code>Option Explicit\nSub Fabrics()\n    'assume data is in column A\n    Dim wsSrc As Worksheet, wsRes As Worksheet\n    Dim vSrc As Variant, vRes As Variant, rRes As Range\n    Dim RE As Object, MC As Object\n    Const sPat As String = \"^(.{6})\\s*(.{5})\\s*(.{4})(?:.*1\/(\\S+))?\"\n        'Group 1 = style\n        'Group 2 = fabric\n        'Group 3 = colour\n        'Group 4 = size\n    Dim colF As Collection, cF As cFabric\n    Dim I As Long\n    Dim S As String\n    Dim V As Variant\n\n'Set source and results worksheets and ranges\nSet wsSrc = ActiveSheet\nSet wsRes = ActiveSheet\n    Set rRes = Selection\n\n'Read source data into array\nvSrc = Selection.Resize(columnsize:=4)\n\n'Initialize the Collection object\nSet colF = New Collection\n\n'Initialize the Regex Object\nSet RE = CreateObject(\"vbscript.regexp\")\nWith RE\n    .Global = False\n    .MultiLine = True\n    .Pattern = sPat\n\n    'iterate through the list\n\n'Test for single cell\nIf Not IsArray(vSrc) Then\n    V = vSrc\n    ReDim vSrc(1 To 1, 1 To 1)\n    vSrc(1, 1) = V\nEnd If\n\nFor I = 1 To UBound(vSrc, 1)\n    S = vSrc(I, 1)\n    Set cF = New cFabric\n    If .test(S) = True Then\n        Set MC = .Execute(S)\n        With MC(0)\n            cF.Style = .submatches(0)\n            cF.Fabric = .submatches(1)\n            cF.Colour = .submatches(2)\n            cF.Size = .submatches(3)\n        End With\n\n    ElseIf .test(vSrc(I, 1) &amp; vSrc(I, 2) &amp; vSrc(I, 3)) = False Then\n        cF.Style = S\n    Else\n        cF.Style = vSrc(I, 1)\n        cF.Fabric = vSrc(I, 2)\n        cF.Colour = vSrc(I, 3)\n        cF.Size = vSrc(I, 4)\n    End If\n    colF.Add cF\nNext I\nEnd With\n\n'create results array\n'Exit if not results\nIf colF.Count = 0 Then Exit Sub\n\nReDim vRes(1 To colF.Count, 1 To 4)\n\n'Populate\nI = 0\nFor Each V In colF\n    I = I + 1\n    With V\n        vRes(I, 1) = .Style\n        vRes(I, 2) = .Fabric\n        vRes(I, 3) = .Colour\n        vRes(I, 4) = .Size\n    End With\nNext V\n\n'Write the results\nSet rRes = rRes.Resize(UBound(vRes, 1), UBound(vRes, 2))\nWith rRes\n    .Clear\n    .NumberFormat = \"@\"\n    .Value = vRes\n    .EntireColumn.AutoFit\nEnd With\n\nEnd Sub\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">5<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved How to stop second run of the code to prevent override data regex vba? <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] One way to tell if the entry has been previously split is as follows If the regex.test fails, then If the results line passes, then the item has been previously split if not, then it is a blank, or a malformed entry Note that a lot of this could be avoided if you were &#8230; <a title=\"[Solved] How to stop second run of the code to prevent override data regex vba?\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/\" aria-label=\"More on [Solved] How to stop second run of the code to prevent override data regex vba?\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,347,401],"class_list":["post-11934","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-regex","tag-vba"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] How to stop second run of the code to prevent override data regex vba? - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] How to stop second run of the code to prevent override data regex vba? - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] One way to tell if the entry has been previously split is as follows If the regex.test fails, then If the results line passes, then the item has been previously split if not, then it is a blank, or a malformed entry Note that a lot of this could be avoided if you were ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-28T20:08:24+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] How to stop second run of the code to prevent override data regex vba?\",\"datePublished\":\"2022-09-28T20:08:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/\"},\"wordCount\":156,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"regex\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/\",\"name\":\"[Solved] How to stop second run of the code to prevent override data regex vba? - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-28T20:08:24+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] How to stop second run of the code to prevent override data regex vba?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] How to stop second run of the code to prevent override data regex vba? - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] How to stop second run of the code to prevent override data regex vba? - JassWeb","og_description":"[ad_1] One way to tell if the entry has been previously split is as follows If the regex.test fails, then If the results line passes, then the item has been previously split if not, then it is a blank, or a malformed entry Note that a lot of this could be avoided if you were ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/","og_site_name":"JassWeb","article_published_time":"2022-09-28T20:08:24+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] How to stop second run of the code to prevent override data regex vba?","datePublished":"2022-09-28T20:08:24+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/"},"wordCount":156,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","regex","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/","url":"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/","name":"[Solved] How to stop second run of the code to prevent override data regex vba? - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-28T20:08:24+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-how-to-stop-second-run-of-the-code-to-prevent-override-data-regex-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] How to stop second run of the code to prevent override data regex vba?"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775193939","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/11934","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=11934"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/11934\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=11934"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=11934"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=11934"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}