A Const must have a value that is determined at compile time. You want to use an expression that must be evaluated at runtime, so you cannot use a Const. The only way to do what you want is this:
If you want VARDEST to be Public, it must be declared outside of a Sub. The two assignment statements here must be inside a Sub.
Note that the value of VARDEST will be calculated only at the point of this assignment statement. If the value of the cell changes later, the value of VARDEST will not.
Another way to do this is
Now VARDEST will always refer to that cell, and if the cell value changes, VARDEST will change.
Bookmarks