Forum Discussion
Looking for a way to track and reference cells in VBA
Good Day, I am searching for a way to track cells so that I know where they have been moved to if a user editing the workbook adds columns/rows, cuts / pastes cells. I have found that I can u...
The point is you set the variable MyRange in the code GetReference.
And in the code PrintReference, you are printing it's properties only without setting it again and since you declared the MyRange variable is the module level variable, it is still have a reference set in GetReference code. And you are not getting it's value in the Immediate Window because you cut that cell and pasted at another location and the range MyRange which was previously set to refer to the Sheet1!A1 is still referring that cell which is now empty and therefore you are not getting its' value.
If you have one such case where you want to track a cell and all it's properties even if it is cut and paste into another location, you may do something like this...
- Select the cell A1 on Sheet1.
- Type MyRange in the Name Box to create a Named Range which refers to the cell A1 on Sheet1.
- Enter any value in A1 on Sheet1, say 'Testing'.
- Now run the code PrintReference given below.
- Now cut the cell and paste it into any available sheet in the file.
- Run the code PrintReference again and you will get it's latest properties.
Dim MyRange As Range
Public Sub PrintReference()
Set MyRange = Range("MyRange")
Debug.Print MyRange.Parent.Name & "->" & MyRange.Address & "->"; MyRange.Value2
End Sub