[RESOLVED] Just want to select a cell!

[pickarooney]

I'm going insane trying to do what should be the simplest of operations in an Excel macro, so hopefully someone can tell me where I'm going wrong.

I have a spreadsheet with several worksheets, containing various types of data in the cells. Some of the cells are formatted as a custom date, so they appear on screen as "Monday 07 Nov". Their values are stored in the standard Excel way, as long integers counted from 1st January 1900, in this case 38663.

Now, everything in the spreadsheet has to be translated, including these dates.
In order to be able to do that, I need to hard-code the dates as text in the cells. I've tried several methods, and the only one that nearly works is in the code below. If I simply try to change the format of the cell in question with ActiveCell.FormulaR1C1 I get a cyclical error. Therefore, I use an empty cell, copy the contents into it and retrieve the value of this cell into the original cell. For some reason, I am unable to reselect the original cell after performing the operation. The only way I can do it is to explicitly call the range of a cell, but this is useless for a macro.

Basically, how to I tell Excel to remember the current cell it's in, go do something else and then come back to the current cell?

Sub ReformatDates()
 
Dim Storage As String
Dim rRow As Long
Dim cColumn As Long
Dim RangeString As String
 
'use an empty cell to store value
Storage = "Z100:Z100"
 
    'For each cell, if Cell.Format = custom, do this (not written yet)
    
    'Store the position of the starting cell
    cColumn = Selection.Column
    rRow = Selection.Row
   
    'make the temporary cell have the same value as this one
    Range(Storage).Value = Selection.Value
    
    'go to the temporary cell
    Range(Storage).Select
    
    'copy and paste itself as a value
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
    'Try to format the range so the cell can be selected - does not work
    'RangeString = rRow & cColumn & ":" & rRow & cColumn
 
    'column letters are interpreted wrongly and entire chunk of worksheet gets    
    'selected instead
    Range(cColumn & ":" & rRow, cColumn & ":" & rRow).Select
    
    'Does not work
    'Range(RangeString).Select
 
    'This works but it useless
    'Range("D13:D13").Select
    'Format the cell as the text equivalent of the contents of the temporary 
    'cell.
    ActiveCell.FormulaR1C1 = "=TEXT(R100C26,""DDDD DD MMM"")"
 
End Sub

It would be great to be able to do this for every cell in the spreadsheet which is formatted as Custom, but any advancement would be welcome.

There's probably a one-line command somewhere that reformats the selected cell as text in one go, but I can't find it.
Note that reformatting a cell manually as text doesn't work, as in that case I get a longint in the cell instead of a formatted date.