"relative reference" question

I'm new to the whole macro thing and am looking for a simple way to 
understand the "relative reference" when recording a macro.  Can someone shed 
some light on that? also, my macro takes a spreadsheet of 10 columns and x 
rows and formats it.  How would I build the macro so that no matter the 
number of rows, the macro will still work? would i have to write code for 
this or will the record macro option work?  Any advice would be appreciated.  
Thanks
0
newarkj (1)
4/27/2005 3:18:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
376 Views

Similar Articles

[PageSpeed] 54

Hi there,

Your whole intent is unclear.  Can you specify the Ranges/Addresses to which 
you are referring?  Give some examples.  And if need be, post your code.


-- 
Regards,
Zack Barresse, aka firefytr


"newarkj" <newarkj@discussions.microsoft.com> wrote in message 
news:D592FDF8-D8D1-48CA-8C45-ECBAA6DC9491@microsoft.com...
> I'm new to the whole macro thing and am looking for a simple way to
> understand the "relative reference" when recording a macro.  Can someone 
> shed
> some light on that? also, my macro takes a spreadsheet of 10 columns and x
> rows and formats it.  How would I build the macro so that no matter the
> number of rows, the macro will still work? would i have to write code for
> this or will the record macro option work?  Any advice would be 
> appreciated.
> Thanks 


0
zackb (19)
4/27/2005 4:26:31 PM
Try both ways when recording and you will see the difference.

Non-relative will record hard ranges like  Range("A1:A23").Select

Relative will record ranges like  Range(Selection,
Selection.End(xlDown)).Select

and  ActiveCell.Offset(173, 5).Range("A1").Select

The best way to find the bottom of a range is to start from the bottom of the
sheet and come up to the last filled cell(s)

e.g. to find last filled cell in Column A

Sub findbottom()
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
            .Offset(1, 0).Select
End Sub

If you record CTRL + End to go to bottom of used range you get

ActiveCell.SpecialCells(xlLastCell).Select

Be careful with this one.  Excel can overestimate the used range of a sheet.


Gord Dibben Excel MVP




On Wed, 27 Apr 2005 08:18:03 -0700, "newarkj"
<newarkj@discussions.microsoft.com> wrote:

>I'm new to the whole macro thing and am looking for a simple way to 
>understand the "relative reference" when recording a macro.  Can someone shed 
>some light on that? also, my macro takes a spreadsheet of 10 columns and x 
>rows and formats it.  How would I build the macro so that no matter the 
>number of rows, the macro will still work? would i have to write code for 
>this or will the record macro option work?  Any advice would be appreciated.  
>Thanks

0
Gord
4/27/2005 5:05:55 PM
Reply:

Similar Artilces:

getpivotdata relative reference?
I'm making several spreadsheet showing month cashflow data that rolls up from pivot table on another sheet. I've been selecting the cell I want the data to go to, entering "+", then selecting the pivot table cell I want to get the data from. This works great and pops the getpivotdata function into the cell I want it in. BUT, when I copy the cell with the getpivotdata function in it over to subsequent months it copies the exact same reference, and I have to go in and change it manually, or individually add the getpivotdata function to the subseqent cells one at a time. is ...

relative reference
I have a cell formula =SUM(I4,-F4) which is in cell J4. If I insert a column so that j moves to k, I want the formula to adjust to =SUM(J4,-G4). Is this possible, and if so how? thanks...charlie Only if you insert the column to the left of F4 will it do that btw, no need for a SUM formula, =I4-F4 will do Regards, Peo Sjoblom "Charlie" wrote: > I have a cell formula =SUM(I4,-F4) which is in cell J4. If I insert a > column so that j moves to k, I want the formula to adjust to > =SUM(J4,-G4). Is this possible, and if so how? > thanks...charlie > I thought I read so...

Using rangename in relative reference
Say I have 12 months of data in 12 columns of one row. I'd like to create a rangename for the January data, use that rangename in a formula elsewhere, and then copy that formula to the 11 columns to the right. But I don't want to lock in the original rangename cell as an absolute reference when I copy it. Lotus 123 has this flexibility with rangenames, but it seems Excel does not. Am I just missing this feature somewhere? Art Hi Art, create your range name and the edit the refersto formula to remove the $ before the column reference. Charles ______________________ Decision...

"relative reference" question
I'm new to the whole macro thing and am looking for a simple way to understand the "relative reference" when recording a macro. Can someone shed some light on that? also, my macro takes a spreadsheet of 10 columns and x rows and formats it. How would I build the macro so that no matter the number of rows, the macro will still work? would i have to write code for this or will the record macro option work? Any advice would be appreciated. Thanks Hi there, Your whole intent is unclear. Can you specify the Ranges/Addresses to which you are referring? Give some examples...

Excel Relative Reference
On Excel XP, how do you switch back to the normal column/row headings where it would equal A1 rather than the relative which is 1:1? Please let me know how I can fix this. Thanks, Trisha Hi Trisha, You probably refer to the Reference style: Tools>Options>General, uncheck R1C1 Reference Style -- Kind Regards, Niek Otten Microsoft MVP - Excel "Trisha" <anonymous@discussions.microsoft.com> wrote in message news:332901c3fd54$8b9dca40$a001280a@phx.gbl... > On Excel XP, how do you switch back to the normal > column/row headings where it would equal A1 rather ...

Relative references for external data
How do you have relative references for imported external data? The data is in the form of CSV files. I need to refer to files in the same directory as the directory in which the Excel file resides. This directory could be moved around and there would be no problem. The data is automatically refreshed when the workbook is opened. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

Relative Reference #2
Hi, My question is I have a row and a column of values. I am trying to multiply the row by column and display it in a row format. eg Row 3 5 2 5 Column 1 4 5 6 I want the product to be displayed as 3 20 10 30 by using relative reference. It doesnt work for me. Pl. let me know if anyone has any idea. Thanks Hi! Assume: A1:D1 = 3;5;2;5 A3:A6 = 1;4;6;5 =A1*INDEX($A3:$A6,COLUMNS($A:A)) Copy across as needed. Biff "Hari" <Hari@discussions.microsoft.com> wrote in message news:12430877-E274-482E-8F16-856C2...

relative references when cutting/pasting
Hi, I have a cell, say A1, with conditional formatting based on the contents of the cell on the same row, say, 3 columns to the right, specified with a relative reference ($D2). When I copy the cells with the conditional formatting up and down, the cell that determines the formatting goes with it, which is just what I want. The formatting for A5 is based on D5, etc. But I want my users to also be able to cut/paste these cells if they wish and in this case, the formatting does not follow. For example, If I cut cell A1 and paste to A5, the formatting is still based on D1, not D5. I woul...

relative references when copying Sheets containing form controls
I've been given a spreadsheet that I need to take copy various sheets from, the sheets contain form controls, when I copy the sheet to a new workbook, named ranges and so on come across fine, but the references to the input ranges of for example the drop down box controls become hard coded to the original sheet, I don't want this.. Is there anyway to alter the way excel copies so that the references remain relative rather than absoloute???? What did you include in the addresses for those references? I put a listbox from the forms toolbar on a worksheet. If I used a range like: $b$...

Relative reference Chart data
Hi, This is probably easier than I make it out to be but here goes. I'm making a list of references I've been using for a research paper in excel and because the list is quite large (50 sources) I wanted to visually depict how useful each reference is. So I created a bubble chart with "relevance" on the x-axis and "quality of the source" on the y-axis which references a 1-4 scale for relevance and quality. That works fine, but when I want to sort references, let's say by date, the references for the chart data are absolute, which means if a Row is sorted up or ...

Relative Reference to Multiple Worksheets
How do I copy a formula that refers to another worksheet but uses a relative worksheet name For example, I have worksheets labelled Emp1, Emp2, Emp3, etc. I have a summary worksheet with rows for each employee. I want to be able to type in a formula to refer to a specific cell in the EmpX worksheet and copy that formula down throughout the entire summary worksheet. I have not been able to type in a relative reference for a worksheet - I have to enter the worksheet as Emp1 - and then when I copy it down to the next row (and want Emp2), it stays Emp1. Have a look at http://www.j-walk.com ...

Relative references to named range
On behalf of my newphew, I received this query: "Quick one - I have a spreadsheet with very long formulas that reference a whole lot of variables in the same column. I tried naming the cells so that writing of the formula is easier to do. However, I need to copy the column across for other entities of varying nature, and these copied cells refer to the same named cell. Can I name a cell and copy it sideways so that it retains some sort of name with allusion to the column that it is in? Andrew van Gruting" I have tried copying a formula with a named range and, as expected, it maint...

Where can I find the relative reference icon to create a macro?
I am creating a macro on Excel 2003. I want the macro to run relative to the position of the active cell so I need to select "relative reference", but the relative reference box does NOT come up. Help shows that it is on the stop recording toolbar, but I don't know where that toolbar is either. I started the macro and stopped it, but "relative reference" is no where in my screen. How can I find the icon? Iliana Tools>Customize>Toolbars. Select "Stop Recording" toolbar>Close Stop Recording Toolbar should now be visible on sheet. Hit &quo...

Conditional Formatting with Relative Reference. #2
Conditional Formatting with Relative Reference. Sample workbook: A B C D E F 1. 4.5 6 7.4 8.3 10 2 2. 1 2 3 4 3 2 3. 1 2 3 4 5 6 4. 6 5 4 3 2 1 Hi, How can Excel 2003 workbook be designed so that if a value of a cell is smaller or greater than the value of the cell, then color of the cell is auto formatted ? Formatting of the cell is as follows: Green if value is greater and Red if the...

Relative reference to another file
Hi- I am trying to create a formula with a relative reference to a cel that is in another workbook. I can only find information on relativ referencing within a workbook. I am trying to use the ..\ concept to go up one level to find the fil works in Excel, but keep getting formula errors. Any input is greatly appreciated -- annabe ----------------------------------------------------------------------- annabel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2652 View this thread: http://www.excelforum.com/showthread.php?threadid=50661 If all workbooks in questi...

relative reference graph
Hi How to make the graph relative in the worksheet. I have some columns of data and next to that is the graph at row 2-60. When I run a macro to list data for example row 100 to row 200, my graph disappear. How to make it relative so I can see the graph no matter what data colimn jump to different area? Thanks Daniel Daniel; I would run your data through a pivot table and then create the graph from there. You can see an example here (file named Man Hour Revenue Chart.xls). http://groups.google.com/group/excel-applications-and-spreadsheet-programming You can select and deselect all ...

Relative Reference in Conditional Formattin
I can't get relative references working in a conditional formatting formula in Excel 2003. Can someone help me with this? I'm sure it must be something simpe I'm missing. Here's what I want to do: - Apply a conditional format to a large area of a spreadsheet to show when the cell in the column to the left of each cell has a higher numeric value - I'd also like to have the ability to insert columns and have them inherit the same conditional formatting. If i have to do this by painting, that's fine. Here are the formulas I've tried so far: =OR(C[-1...

How can I enable the "Record Using Relative References" option in.
When I try to record a macro I am not given th option to use relative references. The manual suggests that I should have that option. Is there a setting somwhere that might enable this feature? Thankyou -- Chris Hi when you start recording a macro (after pressing OK) you should have a toolbar with two icons - the stop recording icon and the relative reference icon - if you can't see it, once you've pressed OK choose view / toolbars / stop recording and it will come back again. To change from absolute to relative recording the relative recording icon needs to be indented. Che...

Macro-Relative Reference
Excel's help page Create a Macro provides that you should go to the "Stop Recording Toolbar" and click Relative Reference so that it is selected. When I do that, no toolbar appears and all that happens is that it stops recording. Excel 2002. TIA for any help. Baldeagle Give this a try Start recording another macro, then goto Tools>Customise and select the Toolbars tab, then check the Stop Recording checkbox And don't close it by hitting the "X" when recording a macro or you will lose it again. -- HTH Bob Phillips "Baldeagle" <Baldeag...

relative reference to previous sheet in formulas?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello. <br><br>the simplest way to ask this seems to be to ask the question before wasting anybody's time on details. <br><br>is this possible? <br> =('previous sheet relative to this one'!H22) <br><br>the longer version is this: <br> I have a worksheet that will grow from one sheet to 52 sheets as the weeks of the year roll by. <br><br>I will need to reference a cell in the previous week's worksheet as a starting point in the current ...

Conditional Formatting with Relative Reference.
Conditional Formatting with Relative Reference. Sample workbook: A B C D E F 1. 4.5 6 7.4 8.3 10 2 2. 1 2 3 4 3 2 3. 1 2 3 4 5 6 4. 6 5 4 3 2 1 Hi, How can Excel 2003 workbook be designed so that if a value of a cell is smaller or greater than the value of the cell, then color of the cell is auto formatted ? Formatting of the cell is as follows: Green if value is greater and Red if ...

Relative Reference
I have spent much time this morning trying to find the answer to my question. So far no luck. Maybe you can help. I have 2 sheets in a workbook. Sheet 1 is called PChart. Sheet 2 is called Defect Sheet. Defect Sheet, Cell E8 has a value of 5. Cell E9 has a value of 10. In the Sheet called PChart, I want to write a formula that references the values in row 8. If I type the following formula ='Defect Sheet'!E$8, I will get the result of 5. This is correct. The problem is that if I try to copy this formula down the column on the PChart Sheet, I get the same results of ='Defect...

RELATIVE REFERENCE BUTTON
When recording a macro, there is no 'relative addressing' button on the visual basic toolbar. I have tried resetting etc, but to no avail. Help pleeeeaaase. Niel, The relative addressing button is on the Stop Recording toolbar. Right-click any toolbar, and if there's no checkmark on "Stop recording," click it. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Neil Mark" <Neil Mark@discussions.microsoft.com> wrote in message news:95E1B661-F5E1-48EE-9926-A0C841F381BD@microsoft.com... > Whe...

Relative reference autofill increment other than +1
I have often needed to drag and autofill a formula such that row references increment by something more than 1. For example, consider the formula "=sum(A1:A10)" in cell B1. If I have 100 rows of data in column A from A1 to A100 and I want to roll up sums in groups of 10 starting in B1, I would drag my formula down from B1 through to B10. The problem is that the autofill increment will increment A1 to A2 and read "=sum(A2:A11)" and so on down the rows. What I want the row reference increment to be is by 10 so the next formula in B2 would read "=sum(A11:A20)&q...

Recording new excel macro, relative reference button doesn't show
When recording a new macro in excel I want to select the relative reference button in the stop recording box, but the stop recording box does not popup; it's no where to be found. Everything I've read on-line says it just appears, but it doesn't on my system. It works fine on my co-workers machine, and he has the same system as mine. We are both running Windows XP, with excel version 2003. hi, not sure why it isn't popping up on your machine. it does on mine. but you can access it anyway. Tools>customize>toolbar tab>check stop recording scroll down.....