[RESOLVED] Just want to select a cell!

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?

VB Code:

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.

Hi Pickarooney

I've gone about this in a completely different way. Not sure how much of this you need or whether I've overlooked an Excel function which converts date serial numbers back to text. Anyhow, it works fine as a one-off solution.

Function MonthName(intMonth As Integer) As String

Dim vntMonthArray As Variant

vntMonthArray = Array("None", "Jan", "Feb", "Mar", "Apr", _
"May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

MonthName = vntMonthArray(intMonth)
End Function

Function DayName(intDay As Integer) As String

Dim vntDayArray As Variant

vntDayArray = Array("None", "Mon", "Tue", "Wed", "Thur", _
"Fri", "Sat")

DayName = vntDayArray(intDay)
End Function

Sub BuildDateText(strAddress As String)

Dim lngCellValue As Long
Dim strBuild As String

Range(strAddress).Select
'Capture the date serial number
Selection.NumberFormat = "General"
lngCellValue = Range(strAddress).Value
'Use the current cell as a work area for this stored number
'(",2" means start weeks on a Monday)
ActiveCell.Formula = "=WEEKDAY(" & lngCellValue & ",2)"
strBuild = strBuild & DayName(Range(strAddress).Value)
ActiveCell.Formula = "=DAY(" & lngCellValue & ")"
'May want to put something here to prefix <10 with zero!
strBuild = strBuild & " " & Range(strAddress).Value
ActiveCell.Formula = "=MONTH(" & lngCellValue & ")"
strBuild = strBuild & " " & MonthName(Range(strAddress).Value)
ActiveCell.Formula = "=YEAR(" & lngCellValue & ")"
'May want to put something here to prefix <10 with zero!
strBuild = strBuild & " " & Range(strAddress).Value

Range(strAddress).Value = strBuild

End Sub

Sub HardCodeDate() '***Main Procedure***

Dim rngCell As Range

For Each rngCell In Range("myrange") 'ie Target range
rngCell.Select
If Selection.NumberFormat = "mmmm d, yyyy" Then
BuildDateText (rngCell.Address)
End If
Next rngCell
End Sub

Does this help? It is hard coded, but you can replace it with whatever programmatic cell references you want. It replaced "=B7+C7" with "13" where B7 contains 6 and C7 contains 7.

Code:

---

Sub Macro1()
    Range("D7").Formula = Range("D7").Value
End Sub

---

Good Learning and Good Programming!

You need to copy the Text (displayed vs stored value) back into the cell. THopefully the following code will help.

VB Code:

Option Explicit
 
Sub ChangeDate()
Dim WkBook As Workbook
Dim WkSht As Worksheet
Dim MyCell As Range
Dim Temp As String
    Set WkBook = ThisWorkbook
    
    For Each WkSht In WkBook.Worksheets 'loop through all sheets
        For Each MyCell In WkSht.UsedRange ' loop through all cells in the used range
            With MyCell
                If Len(.Text) <> 0 Then 'only apply to the following to cell that have a value
                    Temp = .Text 'get the text (not the value) that is displayed in the cell
                    .NumberFormat = "General" 'change the number form back to default
                    .Value = Temp 'set the cell value equal to the saved text
                End If
            End With
        Next MyCell
    Next WkSht
    
    Set WkSht = Nothing
    Set WkBook = Nothing
End Sub

Hi Guys,

First off, thanks for all your efforts. I was away yesterday, so didn't get time to try your code until now.

Dave, that's quite a different way of thinking to me, I have to say :) I'm going to try your code out in a little bit.

Webtest, I laughed when I saw your post, as I was convinced you had just found a single-line answer that was completely eluding me. Unfortunately, it doesn't seem to work, as even after the replacement I end up with the same thing in the cell, i.e. a value of 38663, represented by the date in written format.

Declan, your code sample looks as though it should work, but when I run it, the loop only executes 4 times, even though each worksheet in my spreadsheet has 6 columns x 236 rows. I put a watch on WkSht.UsedRange.Cells.Count and it returns 4 empty cells. I tried preselecting some cells on the worksheet, and leaving nothing selected before running the macro, but in each case it's the same - just 4 empty cells are looked at.

[edit]I put a watch on wkbook and it seems the macro is running itself on a different file... I think it's because the macro is saved, along with all my other Excel macros in a file called PERSONAL.XLS in my Application Data Folder.

By simply changing Set WkBook = ThisWorkbook to Set WkBook = ActiveWorkbook, the macro works perfectly :)

Thank you very much.
P

Thanks P ...

I have precious little time to spend on the forum, so I often scan things quickly and "shoot from the hip". Some time I am going to have to review this thread thoroughly and find out what it is actually all about, but I've got to get back to real work now!