PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] Just want to select a cell!-VBForums
Results 1 to 6 of 6

Thread: [RESOLVED] Just want to select a cell!

Threaded View

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2005

    Resolved [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:
    1. Sub ReformatDates()
    3. Dim Storage As String
    4. Dim rRow As Long
    5. Dim cColumn As Long
    6. Dim RangeString As String
    8. 'use an empty cell to store value
    9. Storage = "Z100:Z100"
    11.     'For each cell, if Cell.Format = custom, do this (not written yet)
    13.     'Store the position of the starting cell
    14.     cColumn = Selection.Column
    15.     rRow = Selection.Row
    17.     'make the temporary cell have the same value as this one
    18.     Range(Storage).Value = Selection.Value
    20.     'go to the temporary cell
    21.     Range(Storage).Select
    23.     'copy and paste itself as a value
    24.     Selection.Copy
    25.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    26.         :=False, Transpose:=False
    28.     'Try to format the range so the cell can be selected - does not work
    29.     'RangeString = rRow & cColumn & ":" & rRow & cColumn
    31.     'column letters are interpreted wrongly and entire chunk of worksheet gets    
    32.     'selected instead
    33.     Range(cColumn & ":" & rRow, cColumn & ":" & rRow).Select
    35.     'Does not work
    36.     'Range(RangeString).Select
    38.     'This works but it useless
    39.     'Range("D13:D13").Select
    40.     'Format the cell as the text equivalent of the contents of the temporary
    41.     'cell.
    42.     ActiveCell.FormulaR1C1 = "=TEXT(R100C26,""DDDD DD MMM"")"
    44. 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.
    Last edited by pickarooney; Oct 25th, 2005 at 05:57 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width