Activecell.formula - using hybrid relative/absolute refs

[piannetta]

Hi, I'm writing a macro that will scan a list of numbers and post total formulas at certain points. Because the list size is dynamic, the point where the formula is placed may change. The formula needs to refer to the cell adjacent to the end of the list, as well as refer to a cell elsewhere. This last cell is at a fixed point, but of course the formula property only allows A1 cell refs and the formular1c1 only allows RC references.

The formula I'm trying to write is:
ActiveCell.FormulaR1C1 = "=RC[-1]/($E$27+$L$27+$M$27)"

If anyone can provide a tip on dealing with this, I'd appreciate it. In the meantime, I'll keep plodding along.

Thanks,
Pete

 

[PaddyD]

Pete,

welcome to the board!

are you familiar with offset? xlUp ??

have a search here for 'offset last row' or 'last row xlUp' etc. see what you get & post back....

Paddy

 

[piannetta]

Thanks Paddy. I didn't find anything and have figured out a convoluted way of achieving my goal. Appreciate your response.

Cheers,
Pete

 

[PaddyD]

why not post it so we can see what you've done...