{"id":26146,"date":"2022-12-15T23:40:45","date_gmt":"2022-12-15T18:10:45","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/"},"modified":"2022-12-15T23:40:45","modified_gmt":"2022-12-15T18:10:45","slug":"solved-vba-excel-problems-and-questions-closed","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/","title":{"rendered":"[Solved] VBA Excel Problems and Questions [closed]"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-24165102\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"24165102\" data-parentid=\"24164786\" 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>You say you want to &#8221; look for exactly &#8220;5000&#8221; &#8221; and then you look at xlPart in your code. If you want to match the value exactly you need to use xlWhole. More to your question, store the cell you find into a range variable and then reference off of that to replace.<\/p>\n<pre><code>Dim rng As Range\n...\nSet rng = WS.Columns(1).Find(what:=Search, Lookat:=xlWhole, MatchCase:=False)\nrng.Offset(0, 4).Value = Replacement     ' Offsets 4 columns from found cell and sets value\n...\n<\/code><\/pre>\n<p>If you need to find all instances, you could loop through continually shrinking your search range to one row below last find or just loop over the cells in that column without bothering with any Find functions.<\/p>\n<p>Edit:<br \/>\nI added parentheses around the Find parameters. That should fix your compile error and make the above code work. Fixes it in Excel for me. As far as a loop, the following code steps through all used rows in Excel, checking the first column.<\/p>\n<pre><code>Dim i As Integer\n...\nFor i = 1 To WS.UsedRange.Rows.count\n    If WS.Cells(i, 1).Value = Search Then\n        WS.Cells(i, 5).Value = Replacement\n    End If\nNext i\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 VBA Excel Problems and Questions [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] You say you want to &#8221; look for exactly &#8220;5000&#8221; &#8221; and then you look at xlPart in your code. If you want to match the value exactly you need to use xlWhole. More to your question, store the cell you find into a range variable and then reference off of that to replace. &#8230; <a title=\"[Solved] VBA Excel Problems and Questions [closed]\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/\" aria-label=\"More on [Solved] VBA Excel Problems and Questions [closed]\">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-26146","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 v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] VBA Excel Problems and Questions [closed] - 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-vba-excel-problems-and-questions-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] VBA Excel Problems and Questions [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] You say you want to &#8221; look for exactly &#8220;5000&#8221; &#8221; and then you look at xlPart in your code. If you want to match the value exactly you need to use xlWhole. More to your question, store the cell you find into a range variable and then reference off of that to replace. ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-12-15T18:10:45+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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] VBA Excel Problems and Questions [closed]\",\"datePublished\":\"2022-12-15T18:10:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/\"},\"wordCount\":149,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/\",\"name\":\"[Solved] VBA Excel Problems and Questions [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-12-15T18:10:45+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] VBA Excel Problems and Questions [closed]\"}]},{\"@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=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] VBA Excel Problems and Questions [closed] - 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-vba-excel-problems-and-questions-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] VBA Excel Problems and Questions [closed] - JassWeb","og_description":"[ad_1] You say you want to &#8221; look for exactly &#8220;5000&#8221; &#8221; and then you look at xlPart in your code. If you want to match the value exactly you need to use xlWhole. More to your question, store the cell you find into a range variable and then reference off of that to replace. ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/","og_site_name":"JassWeb","article_published_time":"2022-12-15T18:10:45+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] VBA Excel Problems and Questions [closed]","datePublished":"2022-12-15T18:10:45+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/"},"wordCount":149,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/","name":"[Solved] VBA Excel Problems and Questions [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-12-15T18:10:45+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-vba-excel-problems-and-questions-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] VBA Excel Problems and Questions [closed]"}]},{"@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=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","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\/26146","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=26146"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/26146\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=26146"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=26146"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=26146"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}