• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Is there a way to use offset by referencing the cell it is used in?

B

bctyner

New Member
Hello,


I'm trying to automate my data analysis. I have a program that gives me a text file of names, dates, and times that I have to paste into Excel and delimit into columns. It would be great if I could add a line of code to my program to write a formula to be delimited into the last column of data that calculates the difference between the start time and end time of using this program.


For example, it would be easy enough to have it write "=A6-A2" (A6 being the end time, and A2 being the start time), but that would only work for the first row of data. I tried using offset, but quickly learned it can't reference the cell it is used in. I need the formula to say "Look one cell to the left and subtract the value of the cell four cells to the left" and it should do this without knowing what cell it is in.


Any help would be greatly appreciated!


Bryan
 
Use R1C1 references.

[pre]
Code: 
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-4]"
[/pre]
Anything in brackets is relative to the object (active cell), any number w/o brackets is an absolute. E.g,

=R1C[2]

Would refer to a cell in row 1 that is 2 columns to the right of current object.
 
Thanks, Luke. The R1C1 style looks extremely useful; however, I had some trouble writing the reference into a cell without returning an error.


What I ended up doing is naming the columns for start time and end time, and then writing into my program to write the text following all of the data "=endtime-starttime"


This way, when I copy/paste the data into a spreadsheet from the text file and then delimit the data into separate columns, it calculates for me the time the user was logged into the program. I have to pre-format the spreadsheet, but I'll be using the same sheet for a while, so it's no big deal.


Thanks again!
 
Hi bctyner/readers,


For future, this is an option too:

Code: 
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))


Do any math you want with the ROW() and COLUMN().


However, and I'm running Excel 2010, there is no error with self-referencing using OFFSET on my system. And iterative calculation is disabled on my system. This issue with OFFSET must have been corrected in or before Excel 2010.


To use R1C1 notation directly in a cell, without using a macro, you have to enable it in Options.


Asa
 
You must log in or register to reply here.
Back
Top