Obtaining a Variable for a loop in the cell above

Greetings, everyone. First time post! Yippee!

Enough with pleasantries. I need an answer! I've been writing VBA for about a year now, so I'm beginning to get a little more advanced in my coding, although I've run into a stump. I need to write out a loop that will take the reference cell from the cell above it, add 27 to the reference, and so on. Except that reference will always be different. I've tried this, but all I'm getting is the value:

dim rcell as string
rcell = right(cells(index1 - 1,"D"),4)

Obviously that's only going to give me the fact that the cell above says zero. Is there any way to extract the last four numbers of the formula from the cell above?

Any help would be greatly appreciated, as this is beginning to test my fortitude.

EDIT:

I was able to get the formula I needed from the cell above with this code:

dim newform as string
newform = range(cells(index1 - 1,"D")).formula

Now what I need to do is this:

The cell has a formula that is: "=Monthly!R5378"
I need to take the 5378 and add 27 to it. How can I change the formula each time I run the macro?