Return active cell's location/row

I'm having trouble identifing a way to return a location for the position of the active cell. I've searched Excel help with "Position, location, return, activecell, etc." and I can't seem to figure this out. I know that it's possible, so that's why I'm on here!

...

Ok, say the active cell is currently "F1", and I need the location "F1" to identify the ROW to be used in a formula later, how would I go about that?

The current contents of cell "F1"' will be "REPLACE", but I need to change the words "REPLACE" in "F1" and other cells labeled "REPLACE" in column F to the following formula (where the "1" in "A1" is is the current row):

Please Login or Register  to view this content.

The only variable in the code will be the "A1" which will change depending on the row.

I hope that's clear enough.

Thanks!
Ryan

You may find it easier inserting the formula using R1C1 notation

=IF(A1>0,VLOOKUP($A1,$Z$53:$AD$163,3)," ")

in VBA terms

Please Login or Register  to view this content.

The advantage of using R1C1 is you can apply things in "batches", the references in the cells are relative to the cell in which the formula resides...

eg assume cell in which formula goes is Z10

RC = Z10 ... no adjustment either to Row or Column

R[2]C[-1] = Y12 ... [ ] indicates a relative adjustment

RC1 = $A10 ... column fixed but row relative

R2C1 = $A$2 ... fixed placement (no [ ])

foxguy, I think the key to this process is the following:

Originally Posted by rziegler

The only variable in the code will be the "A1" which will change depending on the row.

By active cell the OP (I believe) means the formula must adjust the references to A1 such that they are relative to the row in which the formula is placed... it is my personal opinion that R1C1 is the best option esp. if OP is able to identify the cells to be updated in one go rather than iterating through a range row by row (as it is quicker obviously)

Yes, Donkey, that's exactly what I was trying to say.

And I'd like to do it in "one go" as you say.

It does look like this "R1C1" method would be best for what I'm looking at doing. I'll look into this a little more.

Here is one approach...

Please Login or Register  to view this content.

The above creates a temp column of formula which determine the cells to be updated (based on finding "REPLACE" in F) ... once found those values are replaced by your formula before the temp column formulae are subsequently removed.

I appreciate you going through all that work, Donkey! You really saved me today. I didn't understand the majority of your formula, but I used a loop and eliminated adding a temporary column to get the job done. I also used a combination of a different thread that you helped me in and this one to come up with the following code:

Please Login or Register  to view this content.

Thank you for all your help!!!

Could you not replace all that with the below ?

Please Login or Register  to view this content.

Actually, when you put it that way, I can eliminate the "REPLACE" thing all together with the first part of your code:

Please Login or Register  to view this content.

When I see what I wrote and that this few lines of code would work... it makes me feel like an idiot.

Thanks again for all your help!

Originally Posted by rziegler

When I see what I wrote and that this few lines of code would work... it makes me feel like an idiot.

No need to feel like an idiot - there are always a gazillion ways to skin an Excel cat be it in native XL or VBA... picking the right one from the start is nearly always a guesstimate (in my experience)