Relative vs Absolute References (VBA Code)

Hi,

If I'm recording a macro and specify to use relative 
references, why does the VBA code look as though it is 
specifying absolute cell references?

I.e., 

ActiveCell.FormulaR1C1 = "Test"
ActiveCell.Offset(1,0).Range("A1").Select

Why is A1 listed here...considering I'm running this in 
another part of the worksheet say I42? 

Thanks

Jerry
0
anonymous (74739)
11/4/2003 8:37:45 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
466 Views

Similar Articles

[PageSpeed] 30

What you're seeing is just a by-product of how Excel records macros.
Actually, the Range("A1") part is not needed. So if it bothers you, you can
edit your code like this:

  ActiveCell.FormulaR1C1 = "Test"
  ActiveCell.Offset(1, 0).Select

John Walkenbach
For Excel tips, macros, & downloads...
http://j-walk.com/ss


"Jerry" <anonymous@discussions.microsoft.com> wrote in message
news:047f01c3a313$80f594b0$a101280a@phx.gbl...
> Hi,
>
> If I'm recording a macro and specify to use relative
> references, why does the VBA code look as though it is
> specifying absolute cell references?
>
> I.e.,
>
> ActiveCell.FormulaR1C1 = "Test"
> ActiveCell.Offset(1,0).Range("A1").Select
>
> Why is A1 listed here...considering I'm running this in
> another part of the worksheet say I42?
>
> Thanks
>
> Jerry


0
john9024 (37)
11/5/2003 1:17:17 AM
That may look like an absolute address, but it's not.

	range("a1").select 
would mean to select A1 in current worksheet.

But 
ActiveCell.Offset(1,0).Range("A1").Select
means to come down 1 row (& stay in the same column), then use that cell.

..range("a1") refers to that topleft cell of the this range
"activecell.offset(1,0)"

It's easier to see it than describe it (well, for me).

put this sub in a test workbook:

sub testit()
  msgbox ActiveCell.Offset(1,0).Range("A1").address
end sub

now go back to the worksheet and select a cell (say C9), then run the macro.

Try changing the A1 to C3 and see what happens.

Alan Beban has some notes at Chip Pearson's site that you may want to
read/print that have some more variations for using ranges.
http://www.cpearson.com/excel/cells.htm




Jerry wrote:
> 
> Hi,
> 
> If I'm recording a macro and specify to use relative
> references, why does the VBA code look as though it is
> specifying absolute cell references?
> 
> I.e.,
> 
> ActiveCell.FormulaR1C1 = "Test"
> ActiveCell.Offset(1,0).Range("A1").Select
> 
> Why is A1 listed here...considering I'm running this in
> another part of the worksheet say I42?
> 
> Thanks
> 
> Jerry

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10085)
11/5/2003 1:24:49 AM
This chapter is taken from "Excel 2002 VBA Programmer's Reference"
published by Wrox Press Limited. It shows different syntaxes and
methods for dealing with ranges.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexvba/html/odc_5709_chap05idx.asp

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

>Hi,
>
>If I'm recording a macro and specify to use relative 
>references, why does the VBA code look as though it is 
>specifying absolute cell references?
>
>I.e., 
>
>ActiveCell.FormulaR1C1 = "Test"
>ActiveCell.Offset(1,0).Range("A1").Select
>
>Why is A1 listed here...considering I'm running this in 
>another part of the worksheet say I42? 
>
>Thanks
>
>Jerry

0
11/5/2003 3:16:47 AM
Reply:

Similar Artilces:

Calculation on a field code
I have a field code that returns a month name, like April. I was to also display in my document just the first 3 characters of the field code, or in this case, APR. Can I do that without creating a second field code? It depends on what type of field code your field is, but let's suppose it's a REF field. Then one approach is to use a nested field like this: { QUOTE "{ REF myfield } 2010" \@MMM } (where each pair of {} are the "special field braces" that you can insert using ctrl-F9). Peter Jamieson http://tips.pjmsn.me.uk On 14/04/2010 20:...

Tricky question about pasting references
I have a group of cells that are right next to each other. Call these, A1-D1. The cells reference cells in another sheet, but the cells they are referencing are not necessarily adjacent. For example, A1-D1 will contain (in order) references to Worsheet!C3,Worsheet!D3, Worksheet!D3, and Worksheet!F3. I'd like to start a new worksheet where the arrangement of the references is maintained, except that I am now referencing a different worksheet where the referenced cells are moved to the right. For example, cells C3, D3, D3 and E3 are now NewWorksheet!E3, NewWorksheet!F3, NewWorksheet!F3...

combining related data into one row of data
Access 2003 If I have a table like this with fields and data like this: Table1 Acct - Name ------ value1 - value2 - value3 123------Bob------------10-----------10-------10 123------Sue------------10-----------10-------10 How can I get a query result like this? Acct - Name ------ value1 - value2 - value3 123--- Bob, Sue------20------------20--------20 Any assistance greatly appreciated! RBolling On Aug 2, 4:54 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com> wrote: > There is a generic concatenate function with sample usage athttp://www.rogersaccesslibrary.com/OtherLibraries.asp...

Publisher 2000 vs Office XP
I have Office 2000 Premium installed on my computer. I purchased Office XP Professional. It does not have Publisher or Front Page. I am trying to run the 2000 Pub and FP along with the new XP programs. Front page seems ok but I get a message that Publisher cannot find spell checker and hyphen.. dictionary. Reinstall. I have tried everything but still get the same message. Have you installed all the patches for Publisher 2000? -- "If you don't know where you are going, any road will take you there!" If you mean by patches: MS office updates, yes. I have loaded all a...

Code to Create Chart
Hi, I used the macro recorder to create a bar chart. I would like assistance to simplyfy or make the code more efficient. I've listed the code below. Thanks in adbance for your assistance: Sub CreateWIPChart() Sheets.Add.Name = "List" Sheets("WIPCON").Select Columns("H:H").Select Selection.Copy Sheets("List").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Columns("A:A").EntireColumn.AutoFit Sheets("WIPCON"...

Indirect function
Hi, I struggle to create a macro with INDIRECT function that would jump to different cells. What cell I want to jump to depends on a currently selected cell. This means I need to put a relative reference into the function. Here is my function - I need to replace R1C1 expression with a realtive one (currently selected cell address with the same behavior as R1C1 expression). How can I do that? Application.Goto Reference:= _ "INDIRECT(CONCATENATE(R56C8,""!"",""a"",MATCH(R56C1,INDIRECT(CONCATENATE(R56 C8,1)),false)))" Whatever I do I get follo...

vlookup -- table_array as reference to named range
Dear geniuses, I have price sheets coming from several sources with the same format and products, but containing difference prices. I am trying to build a dataset (table) from these sources which I could then link to Access for querying. Given each price sheet source is a seperate worksheet in a workbook, I have given each sheet the named range of its pricing rate. For example, RATE_165 represents the pricing sheet which contains the prices for the "165" rate; RATE_180 represents the pricing sheet which contains the prices for the "180" rate. I want to be able to...

This code works in one database, but not the other...
I have this code (see below) in a form that is to write an audit record. I have sample database that this code runs just fine in, but when I import the same form and table into one of my production databases, I get an error. "Run-time error '3265': Item not found in this collection." When is stops it stops on " rs!UserName = CurrentUser" I'm not a code writer so have little skill in trouble shooting this problem. Any help would be appreciated Kelvin ++++++++++++++++++++ Sub WriteAuditUpdate(txtTableName, lngRecordNum, txtFieldName, OrgValue, CurValue) ...

relative path...
Hello, Using VC++6, I have created an MFC application. My .exe utilizes an outside folder named 'MyFolder'. Presently, I have used the following code in my .exe to access 'MyFolder': CString InitialPath="C:\\Documents and Settings\\Main\\Desktop\ \MyFolder\\"; 'MyFolder' is located in the same folder as my '.exe' What relative path could I use so '.exe' can access 'MyFolder'? Thanks in advance, RABMissouri2007 "RAB" <rabmissouri@yahoo.com> ha scritto nel messaggio news:1188483407.448383.320730@g4g2000hsf.goo...

How to add column(s) that show Yes or No indicating whether that record came from a related table
I had to merge three tables together, and now a request has been made to show (in the merged flat file) in which report the data exists. So I will be adding three columns, and there needs to be a streaming Yes or No in each column, indicating whether the Person's name matches to each file. Is there a way to do this? I am stumped, but mainly b/c I am new to Access and am learning how to connect some of these queries to result in the desired output. Any suggestions would be appreciated. > I had to merge three tables together Why? As in why would you want/need to do that? That is comp...

I want chart source data to be relative references, not absolute.
Everytime I try to enter the chart source data as a relative reference, Excel automatically adds the dollar signs to make the references absolute. I have 12 groups of columns repeating data above the graphs. (The rows and the columns are identical, except for the data entered in the variable cells.) If the cell source data were relative, I could copy the original chart over 11 times and be done. Since it is not, I have to go in each chart and manually edit the source data to adjust the column references. This is a pain. Also, if we delete or add any groups of column data, which we...

Duplicate a chart with relative formulas
If I have several sheets, each with data for a particular year, how can I copy a chart from 1 year to all the others, making sure the data references are to the respective year? Duplicate the sheet that has the chart, then replace the data on the copied sheet. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 3/23/2010 9:29 PM, Bobhoe wrote: > If I have several sheets, each with data for a particular year, how can I > copy a chart from 1 year to all the others, making sure the data references > are to the respective year? ...

Named Range that uses "relative" range
BTW - Excel 2003. I have some data in a table with a column for Jan (column A), Feb (column B), etc. for Sales in Jan, Feb, etc. In the past, I had array formulas that added up each month as needed, using an Array Sum, starting with Jan formula were refer to A$1:A$99. As I copied this formula for Feb, the range I added up was relative, so changed to B$1:B$99, etc. Now I am trying to switch to some cleaner Named Ranges, such as Sales_Jan, Sales_Feb, etc.). My add Array Sum formulas have been changed to use the Named Ranges. Unformtunately, my formula for Jan uses Sales_Jan, wh...

Security Update error code
The automatic security download from Microsoft has tried several times to download Microsoft .NET Framework 1.1 SP1 Security Update for Win2000 and WinXP KB979906 and the installation has failed. Error code is O or 0x643. I am using WinXP Home Edition, Ver 5.1 and SP3. No help from MX on-line do-it-yourself stuff. Not sure what to do next. Any help or suggestions? -- Pink Lady [Crosspost to Windows Update newsgroup] OPTION A: See the "How to obtain help" section of http://support.microsoft.com/kb/979906 For home users, no-charge support is available by calli...

MS Access vs Informix 4GL
Hello all; I am quite comfortable using Access via ODBC however I am now looking at a new position using Informix 4GL on top of SQL Technology on an HP9000 server. Has anyone had experience with Informix from a queries, data reporting, manipulation and GUI design perspective? Is it similar to Access and what would be the reasons for using Informix rather than Access. I am plowing through SQL for dummies in the case that I might have to take hours to do what I can do in minutes with Access. Any info would be greatly appreciated!! Thanks RJ "RJ" <RJ@discussio...

database related problem
I am trying to post this message to the database session, but there seems to be a problem for the past few days posting to it. Here is my question: I have a CRecordset class (call it CMyProblem), which I'm using to update an Access table. I am using MoveFirst() to set the pointer to record 1 and am iterating through the table with MoveNext(). Everytime before moving to the next record, I update a text field. After about the third record, I got a 'Multiple rows were updated' message, which I corrected by changing CMyProblem from snapshot to dynaset. I am using the open() member fun...

Color Codes: Different color with same color Index
Can Anyone shine a light on this: I have a worksheet and did this: activecell.Interior.ColorIndex =40 The cell became salmon orange as I would expect it. I received another version of the same spreadsheet saved by a colleague. All the cells that are salmon-orange in my sheet turn out to be grey in his sheet. When I select a grey cell and run: ?activecell.Interior.ColorIndex again 40 is returned. Even if I once more run activecell.Interior.ColorIndex =40 the cell stays grey. So, I have two sheets, both with cells that have an interior.colorindex of 40 but they show different...

How to insert picture with relative path
Word 2007. When you insert picture with link to graphic file path is absolute by default. It is not convinient when you move doc and picture to other computer. How to edit path? P.S. Word 2003 made relative path. Hi Sevilho, To see how to implement relative paths in Word, check out the solution I've posted at: http://lounge.windowssecrets.com/index.php?showtopic=670027 -- Cheers macropod [Microsoft MVP - Word] "Sevilho" <Sevilho@discussions.microsoft.com> wrote in message news:7DCEEA1C-3426-4AF7-A4DE-5888DC9B9D66@microsoft.com... > Word 200...

OT: VS.Net Opens Files across all 3 monitors. Fix?
Is there any way to get VS to open a file without spreading it all the way across all of my monitors? Thanks "Jona Vark" <noemail@all.com> wrote in message news:reSef.21828$Zv5.8272@newssvr25.news.prodigy.net... > Is there any way to get VS to open a file without spreading it all the way > across all of my monitors? > > Thanks > Odd problem, mine works perfectly. What type of video cards and display drivers are you using? -- ============ Frank Hickman Microsoft MVP NobleSoft, Inc. ============ Replace the _nosp@m_ with @ to reply. "Frank Hickma...

Need code help for controls
Sorry if I don't know what I'm talking about. I saved an spreadsheet as a single file web page and want viewers to be able to use command buttons in the browser to sort the data several ways. I tried using command buttons from the "forms" toolbar with a macro but it doesn't function in the browser (ie6). Then I discovered the "Control" toolbar, but I don't know how to write code to do what I want. Am I looking in the right direction? Thanks for any help. ...

How would I do this?
I'm designing a database using ORM in Visio. I'm stuck on what seems like an easy problem but which has stumped me. I have a Category object Category CatId (primary) ParentCategoryId (zero for root) Name Level I also want each Category to have zero or many Related categories. A category cannot be related to itself. so I created RelatedCategory with 2 facts connected to category Facts 1 Each RelatedCategory has Exactly One Category Each Category has Zero or One Relatedcategory Facts 2 (identical to fact 1) Each RelatedCategory has Exactly One Category Each Category has Z...

How to get the list of "Recent Documents", with code
hi can some tell me how to get the list of recent documents list from windows start -recent document .is there any API to get the list . plz if some one have the solution tell to me thx in advance Here's how I do it using the CRecentFileList object. There are lots of other functions in it, but you'll get the idea. Tom CRecentFileList* pRecentFiles = theApp.GetRecentFilesList(); if(pRecentFiles) { CString csFileNameOnly; theApp.GetRecentFilesList()->WriteList(); int nNumItems = theApp.GetRecentFilesList()->GetSize(); m_nNumItems = 0; for(i...

Access 2007 related documents
I have an Access2007 database and am storing paths to files in a table. Example: a single customer might have 5 external files that are related to the customer I decide not to store the files themselves in the database in an attachment field for various reasons. Everything seems to work ok I just have one question: To open the file (Note: I do not know what type of file will be in the list), I am using this code: Private Declare Function ShellExecute Lib "shell32.dll" _ Alias "ShellExecuteA" (ByVal hWnd As Long, _ ByVal lpOperation As String, _ ...

Interrupting long calculations in Excel? Hi all, I am having a very long calculation in Excel using VBA. Of course I know I can hit ESC or CTRL+BREAK to interrupt the VBA program. But I found du
Interrupting long calculations in Excel? Hi all, I am having a very long calculation in Excel using VBA. Of course I know I can hit ESC or CTRL+BREAK to interrupt the VBA program. But I found due to too long running process, the Excel window stop to respond for very long time, besically it's like "not responding" situation with "not responding" on the windows title bar... It doesn't seem to accept any more key strokes, not to say ESC or CTRL +BREAK. What can I do to rein/stop my program? Thanks Try this to speed up execution Application.ScreenUpdating = FA...

Controls vs. handles
It seems that when I've got a dialog with controls in it the IDC_* names I provide are actually handles that can be used by some functions to refer back to the control. How do I manage to address the control object directly so I can apply its own methods rather than an external function. IOW, what is the name of the object? TIA, Lilith Those IDC's are constants used to write the resource file - simple integers, look in resource.h If you have a control variable (Set up through the dialog editor) and want to get its DLGID use GetDlgCtrlID( ). Are you creating them on the fly? I...