Using a relative or absolute? cell in formula

Hey,

I'm working on my first real macro - so I'm a greenhorn. I've spent a few hours trying to research a solution, but most threads are too technical for me so I'm hoping someone can really help me out.

I am trying to use a formula that references a cell that changes day to day from when I use the macro. I need to use a formula which grabs from a cell in a non concrete location. Let me try and show you what I mean.

From I4:I10, I need a formula like this:

Please Login or Register  to view this content.

The part in red is the cell I have trouble referencing. In other words, I am trying to reference a cell in a separate range, but that cell changes rows. This is probably really vague, so I'm sorry.

Is there a way to name a cell in a range? Because the cell I'm looking for is always D2 in the range I created. But the cells with the formula are not in the range, so I'm having trouble referencing that particular cell. I appreciate any help that is offered, but please keep in mind my knowledge is extremely limited - especially with various functions, etc.

I can't see D" in your formula at all, This is the formula that is written by your code

=IT1*365/IQ$17*10000

Originally Posted by royUK

This is the formula that is written by your code

=IT1*365/IQ$17*10000

Roy I disagree, if the first formula goes into I4 the below:

Please Login or Register  to view this content.

Will become

Please Login or Register  to view this content.

starcraftbud, I'm afraid I don't quite follow what you're asking though...

I just put the formula into A1 to see whether the cell was actually in it.

Wow, thanks for the quick responses guys. I'll try to explain a little better or at least give more details.

Let me first ask this question because if it can be done, it will solve all my problems.

Can a cell in a set range be named? If I am working with a spreadsheet which varies in length, but I construct a range beneath all the information, I am looking to name a cell from my range. The cell is always

Please Login or Register  to view this content.

I haven't figured out how to do that. I can only name cells not in a range.

If that will not work, let me take a step out and re-explain in a broader way what I am trying to do. As I mentioned a have a spreadsheet where the rows vary. Despite the length of the spreadsheet, I set a range of cells 2 rows beneath the last cell with information. Within the range I set, there is a cell that I am trying to reference (which is always cell D2 from the set range). I am trying to reference the cell outside of the range.

OK, so I have my original spreadsheet and lets say cell I4 is where I'm trying to enter a formula. In Cell I4, using the formula I wrote above

Please Login or Register  to view this content.

The R17C[-6] part needs to be cell D2 from the set range. The column is always the same, so a relative reference to -6 is fine, but since the actual row of the cell changes, I'm not sure how to properly reference it within the formula.

starcraftbud, sorry to say but I'm confused... I was waiting to see if RoyUK was going to post but I suspect like me he is confused also...

It would be best to illustrate with a sample workbook, perhaps outline via a couple of sheets what you mean by your varying range etc and also indicate expected results.... from that we should be able to assist, as is I don't have a clue I'm afraid.

starcraftbud, I am working through your code and will post up a solution later... there's quite a bit to get through and I think you can optimise quite a bit.

Attached is a revised version of your file which I think does what you want...re: insertion of formulae into CUSIP BP's column.

I made some changes to your code and I will list here also -- I like to see the code out in full rather than in an embedded file for reference if nothing else.

Your first routine, I shortened to:

Please Login or Register  to view this content.

Your second routine I shortened to:

Please Login or Register  to view this content.

Your final routine I will post in a subsequent replay else it is too long !

You final routine....

Shortened (?!) to the below ...

More importantly I removed a lot of your .Select statements these will slow code and you rarely need to Select anything... also where formats, formulas are common you can update in "batches" rather than processing each individually.

Hopefully this will be useful for you going forward.

Please Login or Register  to view this content.

Re: SOLVED - see "How To" in the Hyperlink Options above this thread.

In answer to your penulimate post you're welcome and I'm glad it worked for you.

The reality is that your question could have been resolved by adding one line of code to your main Format routine, namely the penultimate line:

Please Login or Register  to view this content.

The reality was I had to read through the code to understand what you were trying to achieve and in doing so I figured I would update it where I felt there were gains to be had... in truth given the volume of data being processed the impact of the changes is virtually irrelevant (if noticeable at all!) but as you say you will hopefully learn from it... it's still probably not optimal but does illustrate how .Select (& subsequently Selection) is rarely required... and as you process bigger data sets you will notice the difference performance-wise of a "Select-Free" approach.