Macro for shifting cell reference

blk2051

New Member
Joined
Apr 1, 2009
Messages
4
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5Cbkline%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} span.EmailStyle15 {mso-style-type:personal; mso-style-noshow:yes; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt; font-family:Arial; mso-ascii-font-family:Arial; mso-hansi-font-family:Arial; mso-bidi-font-family:Arial; color:windowtext;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hi All,



I am looking for a macro that will move a cell’s reference down 1 cell each time the macro is run.



For example, say I have this set for formulas in cells D1 to D3 before the macro:<o:p></o:p>
<o:p> </o:p>
<o:p>=A1</o:p>
<o:p>=B1</o:p>
<o:p>=C1</o:p>
<o:p> </o:p>
After I run the macro, I want D1 to D3 to have their formulas changed to:


=A2
=B2
=C2


If I run the macro again, I want D1 to D3 to be:


=A3
=B3
=C3


Is there a way to do this? I appreciate the help!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows
If you're insering cells at the top of A:C then the formula should shift with the cells, if this isn't what you need then a macro would need to be written to your exact formula, not a "for instance" example.
 

blk2051

New Member
Joined
Apr 1, 2009
Messages
4
The data is already present in A-C. For example starting in A1:

1 2 3
5 6 7
8 9 10

At the beginning, D1 to D3 should be:

1
2
3

After the macro, I want the reference to change from A1 B1 C1 to A2 B2 C2 so D1 to D3 reads:

5
6
7

Run the macro again, and in D1 to D3 get:

8
9
10

Basically, I have a large amount of data (30x6120) that I want to be able to look through line by line by looking at a single column. In the data set, each column represents the reading of 1 temperature sensor, and each row is a point in time.

In the end I want to create a bar graph with the data in column D, run the macro, and watch all the little bars move up and down.

Just let me know if that makes sense. Thanks!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows
Try

Code:
Sub update_values()
Static a
If a = 0 Then a = 1
Sheets(1).Range("D1") = "=A" & a
Sheets(1).Range("D2") = "=B" & a
Sheets(1).Range("D3") = "=C" & a
a = a + 1
End Sub

If you're not familiar with code, Sheets(1) works on the left hand sheet tab, not the sheet called "Sheet1", Sheets(2) would be 2nd from left, etc.

Hope this helps
 

blk2051

New Member
Joined
Apr 1, 2009
Messages
4
Perfect, much more simple then I had thought :)

So now when I get to the end of my list, is there an easy way to reset the macro so a=0 again? My lists are of varying length. Can I just write another macro to set a to 0 when I'm done?

Thanks for the help!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,942
Office Version
  1. 365
Platform
  1. Windows
just save the workbook then reopen it, first time you run the macro it should reset to A1,B1,C1.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top