{"id":15,"date":"2011-04-14T21:18:07","date_gmt":"2011-04-15T04:18:07","guid":{"rendered":"http:\/\/www.brightbill.net\/notes\/?p=15"},"modified":"2011-05-24T10:18:21","modified_gmt":"2011-05-24T17:18:21","slug":"relative-addresses-in-excel-macros","status":"publish","type":"post","link":"https:\/\/www.brightbill.net\/notes\/2011\/04\/relative-addresses-in-excel-macros\/","title":{"rendered":"Moving to a Different Cell in an Excel Macro, Relatively Speaking"},"content":{"rendered":"<p><a href=\"http:\/\/www.brightbill.net\/notes\/wp-content\/uploads\/2011\/04\/excel-icon.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignright size-full wp-image-61\" title=\"Excel Icon\" src=\"http:\/\/www.brightbill.net\/notes\/wp-content\/uploads\/2011\/04\/excel-icon.png\" alt=\"Excel Icon\" width=\"259\" height=\"265\" \/><\/a>Primarily a note to myself, but&#8230;<\/p>\n<p>&#8230; in Microsoft Excel, the Macro Recorder defaults to storing an absolute cell address when recording a macro.\u00a0 I seem to typically want a <em>relative<\/em> address, such as &#8220;three cells to the left.&#8221;\u00a0 I can never remember the Visual Basic Syntax for moving the cursor relative to the current cell so I&#8217;m writing it down here.<\/p>\n<p>The Visual Basic property is   <tt>Offset<\/tt> and some sample code is:<\/p>\n<pre><tt>Sub SetStations()\r\n'\r\n' SetStations Macro\r\n' Macro recorded  by thomas brightbill\r\n'\r\n' Keyboard Shortcut: Ctrl+t\r\n'\r\n' This cell is 1.5 LESS than the cell 18 columns to the left\r\n   ActiveCell.FormulaR1C1 = \"=+RC[-18]-1.5\"\r\n' Move right one cell\r\n    ActiveCell.Offset(0, 1).Select\r\n' This cell is 1.5 MORE than the cell 19 columns to the left\r\n    ActiveCell.FormulaR1C1 = \"=+RC[-19]+1.5\"\r\n' Move right one cell\r\n    ActiveCell.Offset(0, 1).Select\r\n' Enter the Text String 'Other' in this cell\r\n    ActiveCell.FormulaR1C1 = \"Other\"\r\n    ActiveCell.Offset(0, 2).Select\r\n    ActiveCell.FormulaR1C1 = \"Steel\"\r\n    ActiveCell.Offset(1, -4).Select\r\nEnd Sub<\/tt><\/pre>\n<p>Links to Microsoft Knowledgebase:<\/p>\n<p><a href=\"http:\/\/support.microsoft.com\/kb\/291308\">http:\/\/support.microsoft.com\/kb\/291308<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Primarily a note to myself, but&#8230; &#8230; in Microsoft Excel, the Macro Recorder defaults to storing an absolute cell address when recording a macro.\u00a0 I seem to typically want a relative address, such as &#8220;three cells to the left.&#8221;\u00a0 I &hellip; <a href=\"https:\/\/www.brightbill.net\/notes\/2011\/04\/relative-addresses-in-excel-macros\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"activitypub_content_warning":"","activitypub_content_visibility":"","footnotes":""},"categories":[4],"tags":[8,9,10],"class_list":["post-15","post","type-post","status-publish","format-standard","hentry","category-technology","tag-excel","tag-macro","tag-relative"],"_links":{"self":[{"href":"https:\/\/www.brightbill.net\/notes\/wp-json\/wp\/v2\/posts\/15","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.brightbill.net\/notes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.brightbill.net\/notes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.brightbill.net\/notes\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.brightbill.net\/notes\/wp-json\/wp\/v2\/comments?post=15"}],"version-history":[{"count":30,"href":"https:\/\/www.brightbill.net\/notes\/wp-json\/wp\/v2\/posts\/15\/revisions"}],"predecessor-version":[{"id":77,"href":"https:\/\/www.brightbill.net\/notes\/wp-json\/wp\/v2\/posts\/15\/revisions\/77"}],"wp:attachment":[{"href":"https:\/\/www.brightbill.net\/notes\/wp-json\/wp\/v2\/media?parent=15"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.brightbill.net\/notes\/wp-json\/wp\/v2\/categories?post=15"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.brightbill.net\/notes\/wp-json\/wp\/v2\/tags?post=15"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}