For example I have a formula to create a % column and I need the formula to make an absolute reference to the total in a column so that the value in each row is divided by that total. However, there isn’t always the same amount of rows in the files the macro is applied to so setting an absolute reference to a specific row and column number provides the wrong value. The code in question that I’m trying to adapt is as follows:

Selection.FormulaR1C1 = "=RC[-1]/R53C2"

This code divides the cell to the left of the selection by the absolute reference B53, however sometimes the total for that column is in row 52 or 54 or 55 or some other row so the absolute reference ends up pointing at the wrong cell. I don’t know how to word this question in a less confusing manner but essentially I need a formula to make a absolute reference relative to a particular selection that might change from use to use.