Code to reference cells that have been moved

[ddub25]

For i = 0 To 12
    If Range("SetsP1").Value + Range("SetsP2").Value = 0 And Range("GamesP1").Value + Range("GamesP2").Value = i Then
        Range("CT" & (85 + i)).Value = Server
    End If
Next i

This code is looking up Named Ranges and outputting values into referenced cells. Problem is, I am planning on moving the information on the spreadsheet and although the named ranges will follow the information, the code referencing cells will not. How do you write code for a cell reference that will move with the cell it's referencing if the cell is moved?

 

[yky]

Try using offset to SetsP1 or other named ranges, something like:

Range("SetsP1").offset(x,y).value= Server <-- substitute x and y with the appropriate offset values.

 

[ddub25]

yky, sorry I don't think I explained myself clearly.

Here's a scenario:

Sub TEST()
    Range("CJ93").Value = Range("Stake").Value
End Sub

This code will enter whatever value is in the 'Stake' Named Range (Cell CJ90) into CJ93. If, at a later date I decided to move the contents of CJ90 to a more appropriate location by cutting and pasting in Excel, the code would still do I what I want it to do as the Named Range would move to the new location.

However, if I had the same code, but was not using a named range and just a cell reference instead (as below), and decided to cut and paste the contents of CJ90 to a new location, this code would still be referencing CJ90 and not the new location.

Sub TEST()
    Range("CJ93").Value = Range("CJ90").Value
End Sub

If not using Named Ranges, how can I make the code in the second scenario automatically update or adapt to any changes of location I've made on the excel sheet?

 

[RoryA]

You can't. That's why you should use named ranges, or some other means of finding the cell you need.

 

[ddub25]

ok thanks.