Macro for shifting cell reference

[blk2051]

<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-typeersonal; 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></o>
<o> </o>
<o>=A1</o>
<o>=B1</o>
<o>=C1</o>
<o> </o>
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!

 

[jasonb75]

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]

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]

Try

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]

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]

just save the workbook then reopen it, first time you run the macro it should reset to A1,B1,C1.