{"id":25217,"date":"2022-12-08T18:53:11","date_gmt":"2022-12-08T13:23:11","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/"},"modified":"2022-12-08T18:53:11","modified_gmt":"2022-12-08T13:23:11","slug":"solved-excel-vba-error-cannot-complete-task-with-available-resources","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/","title":{"rendered":"[Solved] Excel VBA error: &#8220;cannot complete task with available resources&#8221;"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-26126411\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"26126411\" data-parentid=\"25998488\" data-score=\"3\" data-position-on-page=\"1\" data-highest-scored=\"0\" data-question-has-accepted-highest-score=\"0\" itemprop=\"suggestedAnswer\" 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>I understood your goal as: single chart per sheet that exists in the <em>All Data<\/em> list<\/p>\n<p>Your code was creating (as @vba4all suggested) too many charts. I added:<\/p>\n<ol>\n<li><code>sheetsHandled as Collection<\/code> to hold the list of sheets that<br \/>\nalready got their charts.<\/li>\n<li><code>sheetName<\/code> to hold the name of the sheet used many times in code.<\/li>\n<li><code>Function StringExistsInCollection<\/code> that looks for the <code>sheetName<\/code><br \/>\nin <code>sheetsHandled<\/code>.<\/li>\n<\/ol>\n<p>So here is the fixed code:<\/p>\n<pre><code>Sub ForecastsCharts()\n\n    Dim ChtOb As ChartObject\n    Dim lw As Long\n    Dim rng As Range\n    Dim RngToCover As Range\n    Dim sShapeName As String\n    Dim shtrng As Range\n    Dim i As Long\n    Dim RowIndex As Long\n    Dim ad As Worksheet\n    Dim col As Long\n    Dim DataRow As Long\n    Dim rw As Long\n    Dim allDataSheet As Worksheet\n\n    Dim sheetsHandled As New Collection 'Collection for chart references\n    Dim sheetName As String ' Name of the sheet being handled (used many times)\n\n    Set allDataSheet = Sheets(\"All Data\")\n    Application.ScreenUpdating = False\n\n    DataRow = 8\n\n    Do Until allDataSheet.Cells(DataRow, 2).Value = \"\" ' Loop through All Data rows\n\n    sheetName = allDataSheet.Cells(DataRow, 2).Value 'Name is memorised here\n\n    If Not StringExistsInCollection(sheetsHandled, sheetName) Then\n\n        sheetsHandled.Add sheetName 'Remember we handled the sheet\n\n        With Sheets(sheetName) ' Output will go to the applicable Portfolio sheet found in column B\n\n        Set rng = .Range(\"B8\").CurrentRegion\n\n        'If Application.CountIf(rng, \"&lt;&gt;\") = rng.Columns.Count Then   ' All data points required\n\n        If Application.CountIf(rng, \"&lt;&gt;\") &gt; 0 Then    ' At least one data point\n\n            With .Shapes.AddChart(Left:=48, Width:=468, Top:=300, Height:=300).Chart\n                .PlotBy = xlRows\n                .ChartType = xlColumnClustered\n                For RowIndex = 2 To rng.Rows.Count\n                    With .SeriesCollection.NewSeries\n                        'This is the series name\n                        .Name = \"='\" &amp; sheetName &amp; \"'!\" &amp; rng.Cells(RowIndex, 1).Address(, , xlR1C1)\n                        .Values = \"='\" &amp; sheetName &amp; \"'!\" &amp; rng.Rows(RowIndex).Cells(1, 2).Resize(1, rng.Columns.Count - 1).Address(, , xlR1C1)\n                        .XValues = \"='\" &amp; sheetName &amp; \"'!\" &amp; rng.Rows(1).Cells(1, 2).Resize(1, rng.Columns.Count - 1).Address(, , xlR1C1)\n                        .ApplyDataLabels AutoText:=True, LegendKey:=False, _\n                            HasLeaderLines:=True, ShowSeriesName:=False, _\n                            ShowCategoryName:=False, ShowValue:=True, _\n                            ShowPercentage:=True, ShowBubbleSize:=False, _\n                            Separator:=\"\" &amp; Chr(13) &amp; \"\"\n                    End With\n                Next\n            End With\n        End If\n        End With\n    End If 'End to if not sheet is handled\n    DataRow = DataRow + 1\n    Loop\n\nEnd Sub\n\nPublic Function StringExistsInCollection(ByRef aCollection As Collection, item As String) As Boolean\n    StringExistsInCollection = False\n    For i = 1 To aCollection.Count\n        If aCollection(i) = item Then\n            StringExistsInCollection = True\n            Exit Function\n        End If\n    Next i\nEnd Function\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">2<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Excel VBA error: &#8220;cannot complete task with available resources&#8221; <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] I understood your goal as: single chart per sheet that exists in the All Data list Your code was creating (as @vba4all suggested) too many charts. I added: sheetsHandled as Collection to hold the list of sheets that already got their charts. sheetName to hold the name of the sheet used many times in &#8230; <a title=\"[Solved] Excel VBA error: &#8220;cannot complete task with available resources&#8221;\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/\" aria-label=\"More on [Solved] Excel VBA error: &#8220;cannot complete task with available resources&#8221;\">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,401],"class_list":["post-25217","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-vba"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>[Solved] Excel VBA error: &quot;cannot complete task with available resources&quot; - 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-excel-vba-error-cannot-complete-task-with-available-resources\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Excel VBA error: &quot;cannot complete task with available resources&quot; - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] I understood your goal as: single chart per sheet that exists in the All Data list Your code was creating (as @vba4all suggested) too many charts. I added: sheetsHandled as Collection to hold the list of sheets that already got their charts. sheetName to hold the name of the sheet used many times in ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-12-08T13:23:11+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-excel-vba-error-cannot-complete-task-with-available-resources\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-vba-error-cannot-complete-task-with-available-resources\\\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#\\\/schema\\\/person\\\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Excel VBA error: &#8220;cannot complete task with available resources&#8221;\",\"datePublished\":\"2022-12-08T13:23:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-vba-error-cannot-complete-task-with-available-resources\\\/\"},\"wordCount\":85,\"publisher\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#organization\"},\"keywords\":[\"excel\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-vba-error-cannot-complete-task-with-available-resources\\\/\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-vba-error-cannot-complete-task-with-available-resources\\\/\",\"name\":\"[Solved] Excel VBA error: \\\"cannot complete task with available resources\\\" - JassWeb\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/#website\"},\"datePublished\":\"2022-12-08T13:23:11+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-vba-error-cannot-complete-task-with-available-resources\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-vba-error-cannot-complete-task-with-available-resources\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/solved-excel-vba-error-cannot-complete-task-with-available-resources\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Excel VBA error: &#8220;cannot complete task with available resources&#8221;\"}]},{\"@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\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008\",\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008\",\"contentUrl\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/wp-content\\\/litespeed\\\/avatar\\\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\\\/\\\/jassweb.com\"],\"url\":\"https:\\\/\\\/jassweb.com\\\/solved\\\/author\\\/jaspritsinghghumangmail-com\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Excel VBA error: \"cannot complete task with available resources\" - 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-excel-vba-error-cannot-complete-task-with-available-resources\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Excel VBA error: \"cannot complete task with available resources\" - JassWeb","og_description":"[ad_1] I understood your goal as: single chart per sheet that exists in the All Data list Your code was creating (as @vba4all suggested) too many charts. I added: sheetsHandled as Collection to hold the list of sheets that already got their charts. sheetName to hold the name of the sheet used many times in ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/","og_site_name":"JassWeb","article_published_time":"2022-12-08T13:23:11+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-excel-vba-error-cannot-complete-task-with-available-resources\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Excel VBA error: &#8220;cannot complete task with available resources&#8221;","datePublished":"2022-12-08T13:23:11+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/"},"wordCount":85,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/","url":"https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/","name":"[Solved] Excel VBA error: \"cannot complete task with available resources\" - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-12-08T13:23:11+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-excel-vba-error-cannot-complete-task-with-available-resources\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Excel VBA error: &#8220;cannot complete task with available resources&#8221;"}]},{"@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\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1778218008","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\/25217","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=25217"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/25217\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=25217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=25217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=25217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}